August 30, 2010 at 11:51 pm
Hi All,
This might be old question but i am not able to find answer for this.
Actually i am looking for some script which can script out users for the selected database and i can run that script to restore the user permission into the database.
When we restore the database from production to the development, users are also get copied from production to development. Are there any script by which i can script out the users from the development database and then restore that database from production. and once database restored from production to development i can just run the script and restore user permission.
I want to use this for both windows and sql logins. Thanks.
August 31, 2010 at 12:28 am
It's been a while since I ran this, but it's one I wrote some time back for SQL 2005.
select 'GRANT ' + permission_name + ' on ' + o.name + ' to ' + u.name
from sys.sysobjects o
join sys.database_permissions p on o.id = p.major_id
join sys.sysusers u on u.uid = p.grantee_principal_id
--where u.name in ('OptionalMane')
order by u.name, o.name
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 31, 2010 at 7:46 am
If you're talking about actually performing a database restore, then your biggest issue isn't the GRANT permissions. It's going to be missing/orphaned users.
Missing users is obvious. Orphaned users is a little bit different - you might have the same user on the new server, but it will still be an orphaned user in the database because the SID is different. You can utilize sp_change_users_login 'report' to get a list of orphaned Windows users. However, every time that you need to restore the database to this lower environment, you will have to do this again.
What I have done is to get sp_help_revlogin (available in posts on Microsoft's site). This will script out the logins from one server, and you can apply them to a different server. This includes specifying the SID from the source. Then, delete the users from the lower environment, and add them back in with this script. Now, when you restore the database, the SIDs will match, and you won't have all those orphaned users.
Note that this script will also script out the password hash (for SQL logins) - not the actual password, but the hash of it. When you then add the user, it stores the hash - so the login will have the same password on the lower environment as the higher environment. This may or may not be desirable for you. But it is also a neat way to back up your logins without compromising the passwords.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 8:05 pm
Hi,
I am getting the following error when i run the above script.
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.
Please advise. Thanks.
August 31, 2010 at 8:13 pm
HI Wayne,
Actually i am here talking about scripting out users from the database not from the server.
what i do each time i do a restore from production to development is
Stqp1 -> Run sp_helpuser statement to get the user and permission information from the database
Step2 -> Restore database
Step3 -> Delete users from the database because restored database will contain production database users.(this is on the restored database - development database)
Step4-> Restore back the old users to the development database from the Step1.
Now here in step4 i have to run that for individual user manually. SO what i want is to create a script in the step 1 from the database. so that i can run that script in step 4 once the database restore.
Please not i am not talking about orphaned users here. Thanks.
September 1, 2010 at 9:13 am
Does this get the information that you're looking for?
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +
') CREATE USER ' + QuoteName(dp.name) +
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
ON sp.sid = dp.sid
WHERE dp.type like '[GUS]'
SELECT dp.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' +
dp.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +
' ON ' + QuoteName(ss.name) + '.' + QuoteName(so.name) +
' TO ' + QuoteName(dp2.name) + ';',
ss.name,
so.name
FROM sys.database_permissions dp
JOIN sys.database_principals dp2
ON dp2.principal_id = dp.grantee_principal_id
JOIN sys.objects so
ON so.object_id = dp.major_id
JOIN sys.schemas ss
ON ss.schema_id = so.schema_id
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 9:32 am
I use this script for moving logins, first one creates a sp in the db and the next executes it with the passwords and user permissions.
When you get the output from the sp just run that in the SQL destination.
Kyle
September 1, 2010 at 3:08 pm
zombi (8/31/2010)
Hi,I am getting the following error when i run the above script.
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.
Please advise. Thanks.
You need to compare the collations between the user database and master. If they are different you need to include a COLLATE clause in your select.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 1, 2010 at 7:27 pm
Hi Wayne,
I ran the script,
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +
') CREATE USER ' + QuoteName(dp.name) +
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
ON sp.sid = dp.sid
WHERE dp.type like '[GUS]'
Which lists out all the users from the database which is good. But it doesn't restore the permissions.
For Example, If i have a user A having DataReader Permission. Then once i restore from production to development, permission and user will be lost. With the above script i can restore the user but can't restore the permission. Is there any way i can do this? Thanks for your help.
September 7, 2010 at 10:01 pm
Hi Guys,
Below combination worked for me. Just run the Script below for the individual database and copy the output and run into the new query window to restore users.
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +
') CREATE USER ' + QuoteName(dp.name) +
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
ON sp.sid = dp.sid
WHERE dp.type like '[GUS]'
GO
SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';'
FROM sys.database_role_members r1
inner join sys.database_principals r2
on r1.member_principal_id = r2.principal_id
inner join sys.database_principals r3
on r1.role_principal_id = r3.principal_id
GO
February 11, 2016 at 7:45 pm
try this ,
it will extract the complete database permission
the script is just too big to copy paste. make sure you are in the database.
it will only create a temp table , so no need to worry
https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a
Thank you
February 11, 2016 at 7:54 pm
hi,
i have written script , can you check .
gallery.technet.microsoft.com/Extract-Database-dfa53d5a
Thank you
May 11, 2017 at 11:18 am
so combining the two will give u this and it worked for me. Run the below in the source database
--step 1 -------------------------------------------------------------------------------
--RUN THIS ON SOURCE DB
--COPY OUTPUT THEN RUN OUTPUT ON NEW SERVER
----------------------------------------------------------------------------------------
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) +
') CREATE USER ' + QuoteName(dp.name) +
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';'
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp
ON sp.sid = dp.sid
WHERE dp.type like '[GUS]'
--step 2 -------------------------------------------------------------------------------
--RUN THIS ON SOURCE DB
--COPY OUTPUT THEN RUN OUTPUT ON NEW SERVER
----------------------------------------------------------------------------------------
set nocount off
IF OBJECT_ID(N'tempdb..##temp1') IS NOT NULL
DROP TABLE ##temp1
create table ##temp1(query varchar(1000))
insert into ##temp1
select 'use '+db_name() +';'
insert into ##temp1
select 'go'
/*creating database roles*/
insert into ##temp1
select 'if DATABASE_PRINCIPAL_ID('''+name+''') is null
exec sp_addrole '''+name+'''' from sysusers
where issqlrole = 1 and (sid is not null and sid <> 0x0)
/*creating application roles*/
insert into ##temp1
select 'if DATABASE_PRINCIPAL_ID('+char(39)+name+char(39)+')
is null CREATE APPLICATION ROLE ['+name+'] WITH DEFAULT_SCHEMA = ['+
default_schema_name+'], Password='+char(39)+'Pass$w0rd123'+char(39)+' ;'
from sys.database_principals
where type_desc='APPLICATION_ROLE'
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' WITH GRANT OPTION ;'
else
state_desc+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
END
from sys.database_permissions
where class=0 and USER_NAME(grantee_principal_id) not in ('dbo','guest','sys','information_schema')
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
+'] to '+'['+USER_NAME(grantee_principal_id)+']'+' with grant option ;'
else
state_desc+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
+'] to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
end
from sys.database_permissions where class=1 and USER_NAME(grantee_principal_id) not in ('public');
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON schema::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON schema::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.schemas sa on
sa.schema_id = dp.major_id where dp.class=3
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON APPLICATION ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON APPLICATION ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.database_principals sa on
sa.principal_id = dp.major_id where dp.class=4 and sa.type='A'
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join
sys.database_principals sa on sa.principal_id = dp.major_id
where dp.class=4 and sa.type='R'
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON ASSEMBLY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON ASSEMBLY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.assemblies sa on
sa.assembly_id = dp.major_id
where dp.class=5
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON type::['
+SCHEMA_NAME(schema_id)+'].['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON type::['
+SCHEMA_NAME(schema_id)+'].['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.types sa on
sa.user_type_id = dp.major_id
where dp.class=6
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON XML SCHEMA COLLECTION::['+
SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON XML SCHEMA COLLECTION::['+
SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'];'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.xml_schema_collections sa on
sa.xml_collection_id = dp.major_id
where dp.class=10
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON message type::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON message type::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.service_message_types sa on
sa.message_type_id = dp.major_id
where dp.class=15
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON contract::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON contract::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.service_contracts sa on
sa.service_contract_id = dp.major_id
where dp.class=16
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON SERVICE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON SERVICE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.services sa on
sa.service_id = dp.major_id
where dp.class=17
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.remote_service_bindings sa on
sa.remote_service_binding_id = dp.major_id
where dp.class=18
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON route::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON route::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.routes sa on
sa.route_id = dp.major_id
where dp.class=19
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.fulltext_catalogs sa on
sa.fulltext_catalog_id = dp.major_id
where dp.class=23
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.symmetric_keys sa on
sa.symmetric_key_id = dp.major_id
where dp.class=24
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON certificate::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON certificate::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.certificates sa on
sa.certificate_id = dp.major_id
where dp.class=25
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.asymmetric_keys sa on
sa.asymmetric_key_id = dp.major_id
where dp.class=26
insert into ##temp1
select 'exec sp_addrolemember ''' +p.NAME+''','+'['+m.NAME+']'+' ;'
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
where m.name not like 'dbo';
select * from ##temp1
May 11, 2017 at 10:53 pm
glad it worked for you. thank you for reviewing the script
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply