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] TOGRANT INSERT ON SCHEMA::[schemaname] TO
GRANT UPDATE ON SCHEMA::[schemaname] TO
GRANT DELETE ON SCHEMA::[schemaname] TO
GRANT EXECUTE ON SCHEMA::[schemaname] TO
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]