SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grant persmissions to database


Grant persmissions to database

Author
Message
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1789
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
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3848 Visits: 4014
For my case, do you think if I use above grant to schema permissions, it is better to create a customized role first?

Thanks
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1789
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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14939 Visits: 14396
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
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3848 Visits: 4014
Thanks, I will create roles first.
Nassan
Nassan
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 850
I agree with opc.three idea.
manageable and controlable permissions.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search