September 7, 2018 at 10:20 am
I have question how do you handle the orphaned users after database refresh to test or dev. Is there a way dynamically you can fix that rather than manually?
September 7, 2018 at 10:34 am
We have a stored procedure in our database that is run to fix orphaned SQL users in the database. It is run manually after a restore.
September 7, 2018 at 10:41 am
Can you send the script? Also, how do you handle all the permissions in DEV and TEST which are different than production. How do you grant them after the refresh. How will you find what are logins need additional permission?
September 7, 2018 at 10:55 am
Admingod - Friday, September 7, 2018 10:41 AMCan you send the script? Also, how do you handle all the permissions in DEV and TEST which are different than production. How do you grant them after the refresh. How will you find what are logins need additional permission?
The product I help support doesn't have a many users, and those have the same permissions in different environments. If you have need to handle different permissions between DEV, TEST, and Production, you will need to incorporate those into the post restore scripts that need to be run when a database is restored to a different environment.
This is the code I wrote for another purpose but is based on the code I mentioned above. The stored procedure is created in the master database and is invoked like this:
EXEC master.dbo.FixOrphanedUsers @DBName = N'TargetDatabaseName'
USE [master]
GO
/****** Object: StoredProcedure [dbo].[FixOrphanedUsers] Script Date: 9/7/2018 10:48:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FixOrphanedUsers]
@DBName NVARCHAR(128) = NULL
AS
BEGIN
/**********************************************************************************************************************************
Procedure Name: dbo.FixOrphanedUsers
Author: Lynn A Pettis
Site:
Date: 2018-07-09
Purpose:
This procedure will resync the sid of orphaned SQL Server users as result of restoring a target database from another server
or the current server if there has been a change in passwords. It requires that both the SQL Server login (server_principal.name)
and the Database user (database_principal.name) have the same value.
If no database name (null value) or a database name that does not exist in sys.databases is passed in to this procedure it will
abort indicating that the target database does not exist.
To execute this procedure in a database other than master then the master database must be part of the invocation of the procedure.
In master:
exec dbo.FixOrphanedUsers N'UserDatabaseName'; -- replace UserDatabaseName with the actual target database name retaining the quotes
In any other database:
exec master.dbo.FixOrphanedUsers N'UserDatabaseName';
It is not necessary to execute this procedure in the database that you are attempting to fix the orphaned SQL users as you must
specify the database when calling the procedure.
Change Log
-----------------------------------------------------------------------------------------------------------------------------------
Date Developer Version JIRA (If known) Description of change
========== ============================ ======= ================= =================================================================
2018-07-09 Lynn A Pettis 1.0 None Initial version.
===================================================================================================================================
Additioinal comments and explainations if needed (free form, please start with --> (date) (developer name) <cr><lf>):
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
**********************************************************************************************************************************/
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(2000);
BEGIN TRY
IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = ISNULL(@DBname,''))
BEGIN
SET @ErrorMessage = N'Database: ' + ISNULL(@DBName,'-null-') + ' Does not exist -- Procedure is terminating.';
THROW 51000, @ErrorMessage, 1;
END;
DECLARE
@SQLCmd NVARCHAR(MAX)
, @UserName sysname;
DECLARE @SQLCmdTemplate NVARCHAR(MAX);
SET @SQLCmdTemplate = N'
select
[sp].[name] as [ServerPrinciple]
from
[sys].[server_principals] as [sp]
inner join [!DBNAME!].[sys].[database_principals] as [dp]
on ([sp].[name] = [dp].[name])
where
[dp].[type] = ''S''
and [sp].[sid] <> [dp].[sid];
';
SET @SQLCmd = REPLACE(@SQLCmdTemplate, '!DBName!', @DBName);
DECLARE @OrphanedUsers TABLE (
[UserName] sysname NOT NULL
);
INSERT INTO @OrphanedUsers EXEC [sys].[sp_executesql] @stmt = @SQLCmd;
SET @SQLCmdTemplate = N'
USE [!DBName!];
ALTER USER [!UserName!]
WITH LOGIN = [!UserName!];
';
DECLARE [OrphanedUsers] CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
SELECT [UserName] FROM @OrphanedUsers;
OPEN [OrphanedUsers];
FETCH NEXT FROM [OrphanedUsers]
INTO
@UserName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLCmd = REPLACE(REPLACE(@SQLCmdTemplate, '!DBName!', @DBName), '!UserName!', @UserName);
EXEC [sys].[sp_executesql] @stmt = @SQLCmd;
FETCH NEXT FROM [OrphanedUsers]
INTO
@UserName;
END;
CLOSE [OrphanedUsers];
DEALLOCATE [OrphanedUsers];
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply