SQL Server Performance Forum – Threads Archive
SQL server Security
Hi Group, I have joined a new company and over here as such there wasn’t a DBA before, so every person and developer who want to query or update database, grants himself system admin rights or other equivalent rights. There are hundreds of users. Currently the security model we have over here is "Windows Authentication". I can’t make groups or change permissions on windows side, instead I JUST NEED TO MAKE CHANGES OR MOVE USERS TO DIFFERENT GROUPS IN SQL SERVER. I have to do a security audit and list all users in database. Also I have to list what rights and privileges they do have on any databases and which users have system admin roles. I have 2 requirements 1. How to list all users along with the permissions they have on database or on server. I need to know what rights or permissions or privileges they do hold on the database 2. Whats the best way to make different groups and move users accordingly to differnt groups, without recreating users or changing their passwords Remember :: We are using windows authentication security model and I don’t want windows administrator to be SQL Administrator. Also we are using both SQL 2000 AND SQL 2005, so I need solutions for bothWe are using Win 2003 as operating system. Cheers
in SQL 2005 you have two view to get it done.
(a) sys.database_permissions
(b) sys.database_principals EG. select *from sys.database_permissions where grantee_principal_id
=(select principal_id from sys.database_principals where name=’username’)
Madhu
I have checked these views and as such it only list permissions and roles, but how are these view associated with user or login name. How can I associate these views in associating with SQL users? Thanks
i could not follow u. have u seen the type column in sys.database_principals view. Principal type: S = SQL user U = Windows user G = Windows group A = Application role R = Database role C = User mapped to a certificate K = User mapped to an asymmetric key Refer both view in BOL Madhu
Hi,
You can compare it with master..sysusers "sid" or "uid" field. Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
There are hundreds of users, I am sure somebofy must have made script to extract this info.
Its not feasible to map and check for each user role and permissions. Is there a better way out? Cheers
Check sp_helprotect and sysprotects procedure/view and its code may help you to write your own code…
Mohammed U.
yes , you can use the same sp_helprotect wrapper which was mentioned for sql 2000 in sql 2005 also. Madhu
Hi,
refer Script by Satyahttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10504
or something like select sys.database_permissions.*,sys.database_principals.name,sys.database_principals.principal_id,
sys.database_principals.type_desc,sys.database_principals.default_schema_name,sys.database_principals.sid
from sys.database_permissions
join
sys.database_principals
on
sys.database_permissions.grantee_principal_id=sys.database_principals.principal_id Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
Also take a look at this Bloghttp://blogs.msdn.com/lcris/comments/567680.aspx from Laurentiu SQL Security team about SP_HELP_REV_LOGIN updates in SQL 2005. 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.
]]>