July 2, 2008 at 7:34 am
I need to create a new user ID that will have exact same rights as another user ID that needs to be disabled. The ID that needs to be disabled has many roles and rights (database mapping). Is there a way (maybe running a script) that will show all of the security settings of the ID that needs to be disabled PLUS be able to give those rights to the new ID without too much manual work?
Thanks guys.
PH
July 2, 2008 at 12:30 pm
For the below script just change the 2 variable values at the top to whatever the user names aer you are deeling with. Please let me know if you want a script that iterates through all db's and gives results as print instead of select.
SET NOCOUNT ON
DECLARE@OldUser sysname, @newuser sysname
SET@OldUser = 'OldUserName'
SET@newuser = 'NewUserName'
SELECT'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
SELECT'--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'
SELECT'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROMsys.database_role_members AS rm
WHEREUSER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC
SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHEREusr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC
SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHEREusr.name = @OldUser
ANDperm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Regards
July 2, 2008 at 6:16 pm
Thanks Toby. YES, that would be great if you can provide me the "script that iterates through all db's and gives results as print instead of select."
Thank you, sir.
PH
July 2, 2008 at 7:51 pm
Ok, the below still needs the 2 variable values set. The thing is about this one is that it is database user name specific, so if the usernames are the same in the databases it will pick everything up.
declare @db sysname
,@OldUser sysname
,@NewUser sysname
,@SQL Varchar(8000)
Set@OldUser = ''--/-------////---------/////
Set@newuser = ''--\-------\\\\---------\\\\Declare db cursor static for
select name from master..sysdatabases
open db
While 0=0
Begin
fetch next from db
into @db
if @@fetch_status <> 0
break
Set @sql =' use ' + @db + '
If Exists(Select * from sysusers where name = ''' + @OldUser + ''')
Begin
Declare @sql varchar(8000)
Print''Use'' + SPACE(1) + QUOTENAME(DB_NAME()) --AS ''--Database Context''
Declare cmd cursor static for
Select''Exec sp_addrolemember @rolename =''
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') + '', @membername ='' + SPACE(1) + QUOTENAME(''' + @newuser + ''', '''''''') --AS ''--Role Memberships''
Fromsys.database_role_members AS rm
WhereUSER_NAME(rm.member_principal_id) = ''' + @OldUser + '''
Order By rm.role_principal_id ASC
Open cmd
While 0=0
Begin
Fetch Next
From cmd
into @sql
if @@Fetch_Status <> 0
Break
Print @sql
End
Close cmd
Deallocate cmd
Declare cmd cursor static for
SelectCASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
+ SPACE(1) + perm.permission_name + SPACE(1) + ''ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name) + '')'' END
+ SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(''' + @newuser + ''') COLLATE database_default
+ CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END AS ''--Object Level Permissions''
Fromsys.database_permissions AS perm
Join sys.objects obj
on perm.major_id = obj.[object_id]
JOIN sys.database_principals usr
on perm.grantee_principal_id = usr.principal_id
Left Join sys.columns cl
on cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
Whereusr.name = ''' + @OldUser + '''
Order By perm.permission_name ASC, perm.state_desc ASC
Open cmd
While 0=0
Begin
Fetch Next
From cmd
into @sql
if @@Fetch_Status <> 0
Break
Print @sql
End
Close cmd
Deallocate cmd
Declare cmd cursor static for
SelectCASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(''' + @newuser + ''') COLLATE database_default
+ CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END --AS ''--Database Level Permissions''
Fromsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
Whereusr.name = ''' + @OldUser + '''
andperm.major_id = 0
Order By perm.permission_name ASC, perm.state_desc ASC
Open cmd
While 0=0
Begin
Fetch Next
From cmd
into @sql
if @@Fetch_Status <> 0
Break
Print @sql
End
Close cmd
Deallocate cmd
End
'
--Print @sql
Exec(@SQL)
end
close db
deallocate db
July 7, 2008 at 7:18 am
[font="Verdana"]I hope this feature is included in SQL Server 2008[/font]
Regards..Vidhya Sagar
SQL-Articles
July 7, 2008 at 5:30 pm
The problem with including something like this as a feature is that it has too much variability in its application. For instance you might want to create another user or role with all the same rights, on the same server, you might want create the exact same user with the same rights on another server - maybe test or dev, or you might only want to place a user in the same roles as another user.
However, I do think SQL 2005's script-task capability is limited especially when dealing with permissions and security in general. However, if SQL Server did everything for us then we wouldn't have such good job security with good pay 😉
July 7, 2008 at 5:51 pm
Try Idera SQLpermissions tool.
http://www.sqlservercentral.com/Forums/Topic483265-146-1.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply