March 24, 2010 at 10:01 am
Hi,
I have just started playing with the security within sql2005 and have little experience with it... :ermm:
I have created a user 'APPUSR', and then created a role 'DWH' under the database that I want the user to have access to. In the role I have specified (under securables) the tables that I want the role to have access to.
However when I use the login to access SQL server I can't see any of the tables that I have added in the securables section, but I can see all the system views, stored procedures etc.
I know this is a real basic question but how do I get them to have access only to the tables that I need them to read?
Or can someone point me in the way of a nice tutorial that I can follow..... 🙂
March 25, 2010 at 6:17 am
GRANT the DWH role the rights on the tables you need. Or use stored procedures to access the data and grant the EXECUTE right on these procedures.
March 25, 2010 at 9:51 am
Just to verify: did you make the user a member of the role?
Greg
March 26, 2010 at 2:19 am
yep.....made the user a member of the role.
I actually got around the problem temporarily by making the role the owner of the schema that I wanted them to access. Then they could view the tables within that schema.
However that's not ideal, as I don't want them to be the owner, just to have access right to be able to select / insert data.
So how to I not make them the owner, but, grant them access rights to do the above... ?:ermm:
March 26, 2010 at 2:42 am
I'm not sure what you did in the tool, but these are simple statements you'd use.
grant select on (table) to DWH
grant insert in (table) to DWH
That's it. If it's not working, perhaps this user is member of another role that has DENY?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply