September 23, 2006 at 11:14 pm
Hi ,
I need to do "Periodic review of user privileges ", would please help me on this?
September 24, 2006 at 11:37 am
Can you be more specific?
Using sp_helprotect is enough? For example:
1. List the permissions for a specific user with sp_helprotect:
EXEC sp_helprotect NULL, NULL, 'User_Name'
2. You can list permissions for Statements (=S) and Objects (=O) by running:
EXEC sp_helprotect NULL, NULL, NULL, OS
You can run all with sp_msforeachdb.
I hope it will help...
January 18, 2007 at 3:30 pm
I created the following script to show all principals and their roles and object permissions. The old standby Sql 2k stored procedures are no longer recommended by MS for such interrogations. sigh....
script usage: paste the script into Management Studio, connect to any database you'd like to interrogate (you'll need to run under an account that can indeed connect to said db) and then execute the script. Results are best run to text.
Please let me know what you think !
------------------------
SET NOCOUNT ON
DECLARE @principal_id int,
@user_name sysname,
@message varchar(80),
@role sysname,
@permission_name sysname,
@obj_name sysname
DECLARE curPrincipalId
CURSOR FORselectprincipal_id,
name
fromsys.database_principals
whereis_fixed_role=0
orderby name
OPEN curPrincipalId
FETCH NEXT FROM curPrincipalId INTO @principal_id, @user_name
PRINT 'UserRole MembershipObject Permissions'
PRINT '--------------------------------------------------------------------------'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @user_name
DECLAREcurRole
CURSORFOR selecta.name
fromsys.database_principals a,
sys.database_role_members b
whereb.member_principal_id=@principal_id
andb.role_principal_id=a.principal_id
OPENcurRole
FETCHNEXT FROM curRole INTO@role
IF @@FETCH_STATUS 0
PRINT '<>'
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ''+@role
FETCHNEXT FROM curRole INTO@role
END
CLOSE curRole
DEALLOCATE curRole
DECLAREcurObjPermissions
CURSORFOR selectc.name,
b.permission_name
fromsys.database_permissions b,
sys.objects c
whereb.grantee_principal_id=@principal_id
and(c.object_id=b.major_id or c.object_id=b.minor_id)
orderby 1,2
OPENcurObjPermissions
FETCHNEXT FROM curObjPermissions INTO @permission_name, @obj_name
IF @@FETCH_STATUS 0
PRINT ' <>'
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' '+@permission_name + ' '+@obj_name
FETCHNEXT FROM curObjPermissions INTO @permission_name, @obj_name
END
CLOSE curObjPermissions
DEALLOCATE curObjPermissions
FETCH NEXT FROM curPrincipalId INTO @principal_id, @user_name
END
CLOSE curPrincipalId
DEALLOCATE curPrincipalId
January 19, 2007 at 4:26 am
this was a script written by me to get the list of DB and object permissions for all users check if this script helps u.
http://www.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=1777
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply