August 8, 2011 at 9:46 am
Good afternoon:
In the enterprise I work for we have production and staging environments for our Databases and applications. Our Staging-environment DBA is having trouble when restoring a production backup into the staging environment because the existing users in the database are lost after the database is restored (users are not the same in staging and production environment).
So, I would like to create some sort of script or query that could list the existing users and their permissions (maybe roles) so our DBA can generate a list of the existing users and permissions to restore users after the backup is restored.
I would like to know where (what tables) can I find that info, users, permissions and roles (i.e: dbo, datareader...) for every user.
Thanks a lot.
August 8, 2011 at 9:55 am
First, the users are always there. They might not be linked to logins, but the users are in the restored database. If you have the same logins, you can use sp_change_users_login to re-sync them together.
If you have different logins, and are trying to manage permissions, first use roles. Do not ever assign rights to a user for objects. Create a role and add permissions and users to the role. Then you can re-sync the logins to a user/role in the restore.
For finding permissions, fn_my_permissions is a good way to do this. You can run this on the production servers, get a list of permissions, and then add them to the staging server. You might just need to create a new user in your script each time and add them to the database with an existing login mapping.
fn_my_permissions - http://msdn.microsoft.com/en-us/library/ms176097%28v=SQL.90%29.aspx
create user - http://msdn.microsoft.com/en-us/library/ms173463.aspx
In terms of scripts, think about a few of these:
http://www.sqlservercentral.com/scripts/31990/
August 8, 2011 at 1:58 pm
Execute the script below against your production database server under the context of the database for which you wish to export users and the resulting script output will form the statements required to create the logins (windows and sql) retaining any SIDs and SQL server login passwords 😉
use MYDB
SELECT 'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid)) + ' WITH PASSWORD = ' +
MASTER.sys.fn_varbintohexstr(SL.PASSWORD_HASH) + ' HASHED, SID = ' +
MASTER.sys.fn_varbintohexstr(SL.SID) + ', DEFAULT_DATABASE = ' +
QUOTENAME(SL.DEFAULT_DATABASE_NAME) + ', DEFAULT_LANGUAGE = ' +
QUOTENAME(SL.DEFAULT_LANGUAGE_NAME) + ', CHECK_EXPIRATION = ' +
CASE SL.IS_EXPIRATION_CHECKED
WHEN 1 THEN 'ON'
ELSE 'OFF'
END +
', CHECK_POLICY = ' +
CASE SL.IS_POLICY_CHECKED
WHEN 1 THEN 'ON'
ELSE 'OFF'
END
FROM sys.database_principals dp
INNER JOIN SYS.SQL_LOGINS SL ON DP.SID = SL.SID
WHERE dp.type <> 'R' AND dp.principal_id > 4 AND SL.TYPE = 'S'
UNION
SELECT 'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(SP.SID)) +
' FROM WINDOWS WITH DEFAULT_DATABASE = ' +
QUOTENAME(SP.DEFAULT_DATABASE_NAME) +
', DEFAULT_LANGUAGE = ''' + SP.DEFAULT_LANGUAGE_NAME + ''''
FROM SYS.DATABASE_PRINCIPALS DP INNER JOIN
SYS.SERVER_PRINCIPALS SP ON DP.SID = SP.SID
WHERE dp.type <> 'R' AND dp.principal_id > 4
AND SP.TYPE <> 'S'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 8, 2011 at 4:31 pm
I think the issue here is preserving the users and permissions from the test database before restoring over it, and of course losing the existing permissions.
Tables you need to look at are
master.sys.server_principals
yourdb.sys.database_principals
yourdb.sys.database_role_members
yourdb.sys.database_permissions
I can come up with a script tomorrow that reverse engineers the SQL to replay the permissions if you'll help test it out for me. I have one for SQL2000 but its time to update it to SQL2005+
---------------------------------------------------------------------
August 8, 2011 at 4:41 pm
The DBA could just script out the database from SSMS and include object permissions, but I understand that can be slow.
---------------------------------------------------------------------
August 8, 2011 at 4:47 pm
heres a nice little script for the object permissions I just found. I will start off with this and build the rest around it.
http://www.sqlservercentral.com/Forums/Topic453290-146-2.aspx#bm534643
---------------------------------------------------------------------
August 9, 2011 at 5:22 am
try this
-- run this script from the user database
--exec usp_get_object_permissions
--drop proc usp_get_object_permissions
--go
create PROCEDURE [dbo].[usp_get_object_permissions]
@as_objectname sysname = null /***Object Name to check, if null then all objects***/
AS
set nocount on
set quoted_identifier off
--drop and recreate users
select '-- It is not always necessary to drop and recreate the users it will depend on the circumstances under which you need to run this script'
select 'drop user [' + name + ']' from sys.database_principals
where principal_id > 4 and owning_principal_id is NULL
order by name
select 'CREATE USER [' + dp.name collate database_default + '] FOR LOGIN [' + sp.name + ']'+
case dp.type
when 'G' then ' '
else
' WITH DEFAULT_SCHEMA=['+dp.default_schema_name + ']'
end
as '-- by default Orphaned users will not be recreated'
from sys.server_principals sp
inner join sys.database_principals dp on dp.sid = sp.sid
where dp.principal_id > 4 and dp.owning_principal_id is NULL and sp.name <> ''
order by dp.name
-- Recreate the User defined roles
select '-- server created roles should be added by the correct processes'
select 'exec sp_addrole '+ '"' + name + '"'
from sys.database_principals
where name != 'public' and type = 'R' and is_fixed_role = 0
-- ADD ROLE MEMBERS
SELECT 'EXEC sp_addrolemember [' + dp.name + '], [' + USER_NAME(drm.member_principal_id) + '] ' AS [-- AddRolemembers]
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
where USER_NAME(drm.member_principal_id) != 'dbo'
order by drm.role_principal_id
-- CREATE GRANT Object PERMISSIONS SCRIPT
DECLARE @ls_crlf char(2)
SET @ls_crlf = CHAR(13) + CHAR(10)
--declare @as_ObjectName sysname
set @as_ObjectName = NULL
SELECT state_desc + ' '+ permission_name + ' ON [' + OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + '] TO [' + USER_NAME(grantee_principal_id)+']'
+ @ls_crlf as '-- object permissions'
FROM sys.database_permissions (NOLOCK)
WHERE major_id = ISNULL(OBJECT_ID(@as_ObjectName), major_id)
AND OBJECT_SCHEMA_NAME(major_id) != 'SYS'
ORDER BY USER_NAME(grantee_principal_id),OBJECT_SCHEMA_NAME(major_id), OBJECT_NAME(major_id)
SELECT state_desc + ' '+ permission_name + ' TO [' + USER_NAME(grantee_principal_id)+']'
FROM sys.database_permissions (NOLOCK)
WHERE permission_name = 'VIEW DEFINITION'
ORDER BY USER_NAME(grantee_principal_id)
go
'
edit: I added line 'where USER_NAME(drm.member_principal_id) != 'dbo''
to add rolemembers section so it does not try and add 'dbo' to dbowners
---------------------------------------------------------------------
August 10, 2011 at 7:24 am
you may also want to record who the database owner was before the restore.
add this code:
select 'alter authorization on database::['+db_name()+'] to ['+ suser_sname(owner_sid)+']'
AS 'owner of database when script was created'
from master.sys.databases where name = db_name()
---------------------------------------------------------------------
August 10, 2011 at 8:00 am
I ran your code George but I was missing all the application roles. So I can't use it atm!
August 10, 2011 at 8:09 am
Ninja's_RGR'us (8/10/2011)
I ran your code George but I was missing all the application roles. So I can't use it atm!
darn, I don't use application roles anywhere!
That will have probably have to wait as I am off on hols soon!
---------------------------------------------------------------------
August 10, 2011 at 8:23 am
george sibbald (8/10/2011)
Ninja's_RGR'us (8/10/2011)
I ran your code George but I was missing all the application roles. So I can't use it atm!darn, I don't use application roles anywhere!
That will have probably have to wait as I am off on hols soon!
SELECT COUNT(*) AS Cnt FROM sys.database_principals WHERE type_desc = 'APPLICATION_ROLE'
--98 rows affected!
:-D:w00t:
August 10, 2011 at 12:22 pm
Ninja's_RGR'us (8/10/2011)
I ran your code George but I was missing all the application roles. So I can't use it atm!
Will it work if you change this:
[font="Courier New"]select 'exec sp_addrole '+ '"' + name + '"'
from sys.database_principals
where name != 'public' and type = 'R' and is_fixed_role = 0[/font]
to this:
[font="Courier New"]select 'exec sp_addrole '+ '"' + name + '"'
from sys.database_principals
where name != 'public' and type in ('A', 'R') and is_fixed_role = 0[/font]
August 10, 2011 at 12:28 pm
Now they get generated. But I don't have a dumpable test server to try and run it so I'll have to leave it at that for now.
August 10, 2011 at 4:05 pm
that helps homebrew, cheers,
except the SQL to create an application role is different to a database role (CREATE APPLICATION ROLE ) and also requires a password specified in the create statement, so thats going to be a bit of a problem I should think.
---------------------------------------------------------------------
August 10, 2011 at 11:33 pm
Jportelas (8/8/2011)
Good afternoon:In the enterprise I work for we have production and staging environments for our Databases and applications. Our Staging-environment DBA is having trouble when restoring a production backup into the staging environment because the existing users in the database are lost after the database is restored (users are not the same in staging and production environment).
...
This is a scenario where I like to use SqlCompare:
* Create a 'source backup' database on staging
* Use SqlCompare to fill it with the metadata (all definitions including users) from the staging database
* Restore the production backup over staging
* Delete the restored users (problem with SQL-logins with different SIDs)
* Use SqlCompare to overwrite the metadata on the staging database from the 'source backup' database
This way you save possible differences between staging and production definitions, while using production data.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply