question

Katie 1 avatar image
Katie 1 asked

create database user in sqlserver 2008

All, 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 tec 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 schemasw. He just sees the systemtables tab whe the user tries to connect to the database.User needs to see all the tables under the schema1,schema2, schema3 thanks
sql-server-2008permissionsuser
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
The basic structure of creating a user: 1. `CREATE LOGIN ...` 2. `USE DATABASE ...` 3. `CREATE USER ...` 4. `ALTER USER ... WITH DEFAULT_SCHEMA...` 5. `sp_addrolemember...` Have you got statement 2 in the right place, ie before creating the user within the DB?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image
SirSQL answered
I'm guessing that you mean the user is not able to view a list of the tables in the SSMS GUI tree. You might need to grant view definition ([ http://msdn.microsoft.com/en-us/library/ms175808.aspx][1]). Confirm that the user is in fact able to query the tables themselves. Additionally confirm that you have created the login on the server. [1]: http://msdn.microsoft.com/en-us/library/ms175808.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.