CODEWITHSUNDEEP
sqlsql-serversql-server-2008permissions
iims
iims

Reputation: 313

Create user in SQL Server 2008

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

Upvotes: 0

Views: 3018

Answers (3)

JStead
JStead

Reputation: 1730

Expanding on the above answer.

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

Upvotes: 1

NCGrimbo
NCGrimbo

Reputation: 120

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.

Upvotes: 1

jmoreno
jmoreno

Reputation: 13571

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?

Upvotes: 1

Related Questions