create users, logins and give permissions in sql server

Question

i created a login : bob

i granted him permission to create database using : sp_addsrvrolemember 'dbcreator'

This automatically creates a user under the login bob, & with same name as the login name.

Login - bob
user- bob

Is the user created in master database?

I executed this query : select * from sysusers, but i could not find any user named 'bob' in the table. Why so?

'bob' has the permission to create database.

now i want to grant him permission to create table. so how do i do that? When i am doing it using grant, then it says cannot find user 'bob'? Why so?

grant create table to bob

After that, i connected sql server with login 'bob' Then, i created a database : bobdb i entered into bobdb. Now, how to give create table permission to bob in bobdb

Do i need to add a new user?

marc_s · Accepted Answer · 2011-02-28 09:24:22Z

5

The login is at the server level - you have a login on a server.

The user is at the database level - you can define a different user for a single login in each separate database.

The user is created in the database that is currently active when you run the create user .... statement...

So if you were in the master database in SSMS when you ran that query - yes, then that user was created in the master database and isn't present anywhere else...

answered Feb 28, 2011 at 9:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Phil Helmer · Accepted Answer · 2011-03-02 11:00:10Z

2

Connected to any database as yourself...

CREATE LOGIN Bob WITH PASSWORD='<the password>';

That creates a server-level Login that has only the permissions to connect to any db as the Guest User. Now add the Login to the server-level dbcreator role and connect to the server as Bob.

Issue CREATE DATABASE, you will still only have a Login with the name Bob. That Login will own the database, which you can verify by running this command...

SELECT  sp.name AS LoginName, dp.name AS UserName
FROM    bobdb.sys.database_principals AS dp
        INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE   sp.name = 'Bob'
;

Because the Bob login owns the database, he should be able to run most DDL commands for that database. If a login owns a database, that login can do anything inside of that database, including creating tables.

Granting rights explicitly to user Bob within bobdb would be appropriate if you change the database owner (ALTER AUTHORIZATION ON DATABASE::bobdb TO LoginNameOfNewOwner) an account other than Bob. Then Bob can be added as a user also named Bob and then only grant that user the minimum privileges he needs.

For more information, see the sections linked to the page on db_owner in Books Online

Also, sysusers is deprecated after SQL 2000. Refer to the new system catalog views.

Lastly, K. Brian Kelly posted a good summary of login/user mapping on the SSC site.

edited Mar 2, 2011 at 11:00

answered Feb 28, 2011 at 9:55

Phil Helmer

1,2408 silver badges8 bronze badges

2 Comments

sqlchild Over a year ago

, half of my question is still unanswered

Phil Helmer Over a year ago

Your grant command is correct, but the reason it doesn't work is because the Bob login is the dbo user in the database. There is no database user named bob and CREATE USER Bob FROM LOGIN Bob will get an error message (Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.) The Bob login, which is now operating in the database as the user dbo, should already have rights to create a table. You will only need to use CREATE USER and GRANT CREATE TABLE TO for database users other than Bob.

Collectives™ on Stack Overflow

create users, logins and give permissions in sql server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related