What is purpose of creating user on database level in SQL Server 2008. If you expand some Database - Security - Users and if you add user in this way you cannot login to the SQL Server with same user. So What is purpose to create user on database level?
2 Answers
There's a difference between Logins and Users. A login must be created before that login can be added to a database as a user.
Security > Logins are all the users that can log in to the system. Logins have a password and at least the Public role. Members of the SecurityAdmin role, or higher, can edit logins. As you see, securityadmins (and higher) can edit the granular permissions of logins. Admins can edit the passwords and database access of Logins.
MyDatabase > Security > Users have all the User information for that specific database, but does not allow you to edit login permissions.
If you have DBO on your database, but not Admin on the system you can only edit the user privileges on your database. You can not manage a user's access on other databases or their password.
Adding logins and assigning them to databases can be illustrated programatically. A securityadmin would do this:
CREATE LOGIN MyUser WITH PASSWORD = 'MyPassword'
This login now exists in our instance of SQL server, but has no permissions and is not a user on any database.
To add that user to a databaseThe owner of a specific database would create a USER for that LOGIN:
CREATE USER MyUser FOR LOGIN MyUser
Here are more advanced options for creating logins:
if not exists (select * from master.dbo.syslogins where loginname = N'MyUser')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
CREATE LOGIN MyUser WITH PASSWORD = 'MyPassword', CHECK_POLICY = OFF
END
GO
Here are more advanced options adding a user to a database and giving it the db_owner role:
if not exists (select * from dbo.sysusers where name = N'MyUser' and uid < 16382)
EXEC sp_grantdbaccess N'MyUser', N'MyUser'
GO
exec sp_addrolemember N'db_owner', N'MyUser'
GO
2 Comments
user2457382
Ok, in this example I can login to the SQL server with MyUser login, but on database xy I will have right's assigned to the user MyUser?
BClaydon
Yes, kind of. The second code block adds the user to database xy, but it does not yet have rights. I was hoping CREATE USER FOR LOGIN would illustrate that you would have to add the login as a user to database xy.
A login must be created to gain access to the SQL instance. The login can be granted server roles, so they can do things like administer security or be an admin.
The login is mapped to a user in the database. Rights are granted per database on this user or on a role that the user is a member of.
A login may also have rights to a database through the guest user.
There is, of course, documentation on this.
Comments
