CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2457382
user2457382

Reputation: 349

Create user on database level

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?

Upvotes: 4

Views: 3033

Answers (2)

BClaydon
BClaydon

Reputation: 1970

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

Upvotes: 4

Sam
Sam

Reputation: 7678

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.

Upvotes: 2

Related Questions