x

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, 2011 at 01:40 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 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, 2011 at 02:40 PM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(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][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
more ▼

answered Jul 11, 2011 at 03:57 PM

SirSQL gravatar image

SirSQL
4.8k 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x89
x31

asked: Jul 11, 2011 at 01:40 PM

Seen: 1692 times

Last Updated: Jul 11, 2011 at 01:54 PM