Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Grant persmissions to database Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 11:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 946, Visits: 1,773
sqlfriends (3/14/2013)
Chris Harshman (3/13/2013)
sqlfriends (3/13/2013)
...They may need to read and write and also execute stored procedures in the databases...

Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:
GRANT SELECT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT INSERT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT UPDATE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT DELETE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT EXECUTE ON SCHEMA::[schemaname] TO [user or rolename]

http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx



Hi, If I granted permission for above, later if I would like to use a query find out what permission has been granted to this user, is there an easy way to look clearly about it?

Or simply to say how can I query out what permission has been granted to a specific user on a specific database?
Thanks much


I have the following 2 queries that i use:

SELECT dbuser.name, dbrole.name
FROM sys.database_principals dbrole
INNER JOIN sys.database_role_members rm
ON rm.role_principal_id = dbrole.principal_id
INNER JOIN sys.database_principals dbuser
ON dbuser.principal_id = rm.member_principal_id
ORDER BY dbuser.name

SELECT pri.name, pro.name
FROM sys.procedures pro
INNER JOIN sys.database_permissions per
ON pro.object_id = per.major_id
INNER JOIN sys.database_principals pri
ON pri.principal_id = per.grantee_principal_id

The first one shows any user roles (db_owner, db_reader, db_writer .....)
The second shows any stored procedures.

The way I prefer to do permissions is with SP's being the only way to access the data preferably but if adhoc queries are going to be run from the app give data reader/datawriter (if updates need to be done out side of SP's which personally i never like).

If i want to go and create a custom level of access i prefer creating a custom DB role for the application put the application user in that role then assign all needed permissions to the role not the user. The benifit of this is that if you want a second user for the application for some reason (or a second app that has the same level of access but a different user) it makes things easier.

EDIT: You should be able to work with the sys.database_permissions table to get any thing else you may need.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1431160
Posted Thursday, March 14, 2013 12:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:46 PM
Points: 1,776, Visits: 3,222
For my case, do you think if I use above grant to schema permissions, it is better to create a customized role first?

Thanks
Post #1431180
Posted Thursday, March 14, 2013 1:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 946, Visits: 1,773
sqlfriends (3/14/2013)
For my case, do you think if I use above grant to schema permissions, it is better to create a customized role first?

Thanks


My preferred method is to use Roles (OR groups in AD) to assign permissions instead of assigning to individual users. Then you document what permissions the role has and if you forget to document adding a user to the role its easy to look at which user is assigned to each DB Role with a simple query.

For the best way as always it depends on the environment and the people at the top.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1431213
Posted Thursday, March 14, 2013 1:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
I like to use Database Roles and only grant permissions to the Roles even if the Role will only have one member initially. Consider that if a person leaves an organization you may delete their Login and Database User but the Role will still be there with the permissions granted to it and the person's replacement can easily be added to that Role and have the same permissions their predecessor had. That is just one benefit of using Roles, there are numerous and there really is no reason not to use them and avoid granting permissions directly to a User.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1431222
Posted Thursday, March 14, 2013 1:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:46 PM
Points: 1,776, Visits: 3,222
Thanks, I will create roles first.
Post #1431224
Posted Monday, March 18, 2013 11:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:36 AM
Points: 36, Visits: 718
I agree with opc.three idea.
manageable and controlable permissions.
Post #1432273
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse