SQL Server Performance Forum – Threads Archive

SQL server security model queries

I have few questions over here on SQL server security model 1. How to create role at server level??
2. Can we combine multiple server role as single role??
3. How can we create Login Groups IN SQL Server same as in windows, so that we can assign permissions to multiple users at same time??
4. If there are several existing roles, how can we script out or query what permissions or rights does this role have??
5. is there a easy way to Creating database role and assigning permissions as in db role we have to check every object and permissions required for select, insert or update function rights.
6. whats function of DRI column in granting permissions.

1. How to create role at server level??
AFAIK… you can not add user defined server level role. for that matter you can not do any alteration/additions/deletion to the existing serverleve role 2. Can we combine multiple server role as single role??
No… you can not do this 3. How can we create Login Groups IN SQL Server same as in windows, so that we can assign permissions to multiple users at same time??
Again…. you can not group sql login at the server level. ofcourse you can do this at the database level using role and grant permission accordingly 4. If there are several existing roles, how can we script out or query what permissions or rights does this role have??
http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm
http://www.sql-server-performance.com/bm_object_permission_scripts.asp 5. is there a easy way to Creating database role and assigning permissions as in db role we have to check every object and permissions required for select, insert or update function rights.
you can query on sys.database_permissions or sys.fn_builtin_permissions. and also Read about Security Catalog Views in BOL. 6. whats function of DRI column in granting permissions.
I couldn’t follow the question
Madhu

6. DRI forbids cascading updates or deletes and please explain what and where you have come accross this on graning permissions. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thanks Madhu for your reply. DRI IS A Column name when we assign permissions to the objects. For example while assigning permissions to a table, there are 5 columns for updating permissions like select, update, insert,delete, exec and DRI. It refers to declarative referential integrity permissions on objects. I am now sure what this means, can you explain this?? Thanks

http://en.wikipedia.org/wiki/Declarative_Referential_Integrity
http://groups.google.com/group/micr…tial integrity&rnum=10&hl=en#66d5684c0684fce2
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>