February 2, 2009 at 10:35 am
If I want a particular user to have SELECT permissions only on certain tables is there someway of achieving this using schemas or do I simply have to go through each relevant table granting SELECT permission?
For example, can I create a schema that is setup with the SELECT permissions on the relevant tables and then add users to that schema or do I have to assign permissions for each user?
Thanks
February 2, 2009 at 10:42 am
You can create a Group and give Select Permission to the appropriate Tables to that group. After that add the Users to that group. Simple and sweet.
-Roy
February 2, 2009 at 10:46 am
Roy, probably a dumb question but how do I create a group??
February 2, 2009 at 10:49 am
My Bad..It is actually termed a Role.
Not group... Stupid me.
-Roy
February 2, 2009 at 10:58 am
Thanks Roy
February 2, 2009 at 12:54 pm
database role best ways to go 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 2, 2009 at 4:31 pm
Lately I had an issue when I moved one of my windows user to active directory group and gave the same permissions to the group as the user, the user started getting error messages.
February 3, 2009 at 12:47 am
Thanks all for your responses.
So would it be advisable to use a schema as well i.e. create a schema and then associate it to a custom database role? I want to ensure that certain database objects are kept seperate from other database objects so my thinking was that the schema would allow me to do that whilst the role would allow me to restrict user access, does this sound sensible?
February 3, 2009 at 6:01 am
Sure, That works fine. One more layer of security. 🙂
-Roy
February 3, 2009 at 8:13 am
yes, I know it should but we started seeing errors from application.
February 3, 2009 at 8:28 am
cherie (2/3/2009)
yes, I know it should but we started seeing errors from application.
Do you remember what kind of error messages you got?
-Roy
February 3, 2009 at 8:28 am
Cherie, your problem may be unrelated. Windows groups cannot be assigned a default schema inside SQL. If the application is not referencing objects with a 2 part identifier (ie [schema].[object]) then it may be relying on the user defaulting to the schema where the objects reside - if the user is not defaulting to that schema then they won't be able to see the objects even if they have permissions to do so.
February 3, 2009 at 10:50 am
Roy Ernest (2/3/2009)
cherie (2/3/2009)
yes, I know it should but we started seeing errors from application.Do you remember what kind of error messages you got?
Login failed message..
April 28, 2010 at 3:55 am
Roy Ernest (2/2/2009)
You can create a Group and give Select Permission to the appropriate Tables to that group. After that add the Users to that group. Simple and sweet.
Hi All,
I'm stuck again with a security problem! I created the database role as recommended and added users etc. All works ok except now I need the members of the db role to be able to execute stored procedures that update, insert and delete data. I thought I'd got around the problem by adding the 'WITH EXECUTE AS dbo' statement to each stored procedure. However I have a stored procedure that extracts data from a linked server; this stored procedure will not work with the 'EXECUTE AS' so I am stuck.
Should I have to add the 'EXECUTE AS' for my users (who are members of the db role) to execute my stored procedures or have I done something wrong/missed something out?
Thanks
April 28, 2010 at 6:02 am
just use the following pattern
GRANT permissions list ON OBJECT::storedprocname to databaserole
valid SP permissions to grant are alter, control, execute, take ownership, view definition
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply