Create user in SQL Server 2008

Question

I have SQL Server user in the database and for some reason, user is not able to view the tables when connecting

Here are the commands that I am using to create that user. I do not want to make the user dbuser the owner of the schema.

create user dbuser for login dbuser'

--on the specific database, I am using these commands.
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'dbuser'
exec sp_addrolemember @rolename = 'db_datawriter', @membername = 'dbuser'

grant select on schema :: [schema1] TO [dbuser]
grant insert on schema  :: [schema1] TO [dbuser]
grant update  on schema :: [schema1] TO [dbuser]
grant execute  on schema  :: [schema1] TO [dbuser]

grant select on schema :: [schema2] TO [dbuser]
grant insert on schema  :: [schema2] TO [dbuser]
grant update  on schema  :: [schema2] TO [dbuser]
grant execute  on schema  :: [schema2] TO [dbuser]

Even though I am granting all insert and update access on the schema.. the user when logged in with dbuser is not able to view the any of the tables from any of the schema. He just sees the system tables tab when the user tries to connect to the database. User needs to see all the tables under the schema1,schema2, schema3

thanks

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Jul 11, 2011 at 20:37

NCGrimbo · Accepted Answer · 2011-07-11 22:05:12Z

1

Based on a workaround found at Microsoft Connect

do the following:

To resolve the issue, you must login as an Administrator, right-click the Database node, choose Properties, Permissions, and grant the "View definition" permission to the user.

answered Jul 11, 2011 at 22:05

NCGrimbo

1302 silver badges10 bronze badges

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

Comments

jmoreno · Accepted Answer · 2011-07-11 20:53:42Z

1

Are you sure that the user is connecting to your db and not master? And that you are creating the user in the right DB?

answered Jul 11, 2011 at 20:53

jmoreno

13.7k6 gold badges66 silver badges103 bronze badges

2 Comments

iims Over a year ago

Yes I am i see the user created, in the db that i wished to create this user.

jmoreno Over a year ago

@pkat: are you sure that user isn't DENYd somewhere? I just tested your code, and it worked for me with the dbo schema. The only thing I changed is the single quote after your create user for login (deleted it), and the schema names. Also, you probably don't need to add any of the rights explicitly, as the addrolemember should take care of that for you.

JStead · Accepted Answer · 2011-07-12 11:50:21Z

1

Expanding on the above answer.

GRANT VIEW DEFINITION ON SCHEMA::[schema1] TO [dbuser];
GRANT VIEW DEFINITION ON SCHEMA::[schema2] TO [dbuser];

answered Jul 12, 2011 at 11:50

JStead

1,73011 silver badges12 bronze badges

1 Comment

iims Over a year ago

intrestingly, this didnt work,I added this to my script and ran, it was still the same issue. But when I followed, NCGrimbo, it worked.

Collectives™ on Stack Overflow

Create user in SQL Server 2008

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related