x
login about faq Site discussion (meta-askssc)

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

more ▼

asked Jul 11 '11 at 01:40 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 108 161 202

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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?

more ▼

answered Jul 11 '11 at 02:40 PM

ThomasRushton gravatar image

ThomasRushton ♦
29.4k 6 9 36

(comments are locked)
10|1200 characters needed characters left

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).

Confirm that the user is in fact able to query the tables themselves. Additionally confirm that you have created the login on the server.

more ▼

answered Jul 11 '11 at 03:57 PM

SirSQL gravatar image

SirSQL
4.6k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1601
x72
x26

asked: Jul 11 '11 at 01:40 PM

Seen: 944 times

Last Updated: Jul 11 '11 at 01:54 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.