August 18, 2011 at 12:42 pm
Hi !
I am about to do restore of database in a environment and i was extracting the security by right clicking the database--Task--GenerateScripts-- selecting the users,databaseroles,schemas and saving it in a file and applying that after the restore. So my question is instead of using the Wizard is there any query to pull up the security.
Thanks in advance.
August 18, 2011 at 12:54 pm
Do you need a script that will just display the memberships and security for that user.
Or do you need something that will generate the scripts to recreate that user with appropriate perms?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 1:41 pm
this is a script I use to generate a script of the security for restores... basically I use this when I am asked to "restore the database, but leave all the permissions the same"... it will give errors which can be ignored under some circumstances... for example, if, prior to the restore, there was an object which had explicit permissions, this will try to recreate those permissions, but if that object no longer exists, we can expect an error.... stuff like that:
/**********SET RESULTS TO TEXT TO ENSURE FULL OUTPUT******/
/**
NOTE: This script should be run once prior to a database restore to generate the
appropriate permissions scripts. Set results to text and retain them
Once the database is restored, the script must be run against the restored database,
AGAIN, the results should be output to text. These results will include "drop schema"
and "drop user" statements which must be copied and run against the restored database
to drop the production users.
so, you will run the script, restore the database, run the output of first script,
then run the output of that script again... it's simpler than it sounds
**/
/*** NOTE- THIS SCRIPT RETAINS PERMISSONS BUT DOES NOT REPAIR ORPHANED USERS
YOU MUST STILL RUN SP_CHANGE_USERS_LOGIN TO REPAIR ORPHANED USERS
***/
SET NOCOUNT ON
print'--paste these results into new query window and run with results to text,'
print'--then execute the drop statement output again'
print'--permissions script for' +db_name()+' on '+@@servername
print ' '
if ((select COUNT (name) from sys.database_principals where type='R' and is_fixed_role =0 and name!='public') >0)
begin
print '--recreate any user created roles'
select 'create role ['+name+'] authorization [dbo]' from sys.database_principals where type='R' and is_fixed_role =0 and name!='public'
end
else
begin
print '--no user created roles to script'
end
print 'go'
print'--grant users access'
SELECT 'EXEC [sp_grantdbaccess] @loginame =['+[master].[dbo].[syslogins].[loginname]+'], @name_in_db =['+
[sysusers].[name]+']'
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
--WHERE [sysusers].[name]
print 'go'
PRINT '--add users to roles'
select 'EXEC sp_addrolemember ' + '@rolename=['+r.name+ '], @membername= ['+ m.name+']'
from sys.database_role_members rm
join sys.database_principals r on rm.role_principal_id = r.principal_id
join sys.database_principals m on
rm.member_principal_id = m.principal_id
where m.name!='dbo'
order by r.name, m.name
print 'go'
print '--object level perms'
select p.state_desc + ' ' + p.permission_name + ' ON [' + s.name +'].['+ o.name collate Latin1_general_CI_AS+ '] TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
print 'go'
print '--grant databasewide permissions'
select p.state_desc + ' ' + p.permission_name +' TO [' + u.name collate Latin1_general_CI_AS + ']' from sys.database_permissions p inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
where p.class_desc='DATABASE'
print 'go'
PRINT'--list of users and schemas to drop'
PRINT' '
select 'select ''drop schema[''+name+'']'' from sys.schemas where name not in('
select ''''+name+''',' from sys.schemas
PRINT '''dropusername'')'
select 'select ''drop user[''+name+'']'' from sysusers where name not in('
select ''''+name+''',' from sysusers
PRINT '''dropusername'')'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply