August 13, 2008 at 2:00 pm
Hi,
We are planning to remove the individual logins in our sql servers in all the test environments first and later in production. Incase the user requests for his login we want to restore his login.Can you tell me how to do this as there will be different db's with different objects and diff permissions on different objects
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
August 14, 2008 at 1:23 am
Re-instituting the logins portion (getting access to the SQL Server itself) is fairly straight-forward:
How to transfer the logins and the passwords between instances of SQL Server 2005[/url]
However, permissions within the database are another matter entirely. You could script out the database including permissions using the given tools. The problem there is how do you separate each individual user's permissions? For your databases, were permissions defined against individual users or were they assigned against database roles?
K. Brian Kelley
@kbriankelley
August 14, 2008 at 6:37 am
Hi,
Mostly by roles. But for some users they were given individually. Can you give me the script to this or any tool.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
August 14, 2008 at 9:15 am
SQL Server Management Studio will do the scripting but it's geared towards objects. There's also Scriptio, but it too is more geared towards objects:
Then there are the 3rd party tools like Apex SQL Doc and Red Gate's SQL Compare.
K. Brian Kelley
@kbriankelley
August 15, 2008 at 10:01 am
I have the following which targets a specific user, but you could easily adapt it to traverse all database principals with
select name from sys.database_principals
where type in ('S','U','G')
and name not in ('INFORMATION_SCHEMA','guest','sys','dbo')
declare @db sysname
,@OldUser sysname
,@NewUser sysname
,@SQL Varchar(8000)
Set @OldUser = 'Oldusername----/-------////---------/////
Set @newuser = 'Newusername'--\-------\\\\---------\\\\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''
From sys.database_role_members AS rm
Where USER_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
Select CASE 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''
From sys.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
Where usr.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
Select CASE 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''
From sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
Where usr.name = ''' + @OldUser + '''
and perm.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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply