sap_help_revlogin.sql

sap_help_revlogin.sql

-- Downloaded from http://blogs.msdn.com/b/saponsqlserver
EXEC('IF OBJECT_ID (''sp_hexadecimal'') IS NOT NULL DROP PROCEDURE sp_hexadecimal')
 
EXEC('CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ''0x''
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ''0123456789ABCDEF''
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue')

EXEC('IF OBJECT_ID (''sap_help_revlogin'') IS NOT NULL DROP PROCEDURE sap_help_revlogin')

EXEC('CREATE PROCEDURE sap_help_revlogin @login_name sysname = NULL AS
-- Version 2.0, 11/13/2012
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @tmpstr2  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
DECLARE @role_name sysname
DECLARE @dbname sysname
DECLARE @dname sysname
DECLARE @dschema sysname
DECLARE @db_role_name sysname
DECLARE @two_ends varchar(1)
DECLARE @permission nvarchar(128)

SELECT @two_ends = NULL
SELECT @permission = NULL
SET NOCOUNT ON;
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ''S'', ''G'', ''U'' ) AND p.name <> ''sa''
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ''S'', ''G'', ''U'' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT ''No login(s) found.''
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = ''/* sap_help_revlogin script ''
PRINT @tmpstr
SET @tmpstr = ''** Generated '' + CONVERT (varchar, GETDATE()) + '' on '' + @@SERVERNAME + '' */''
PRINT @tmpstr
PRINT ''''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''''
    SET @tmpstr = ''-- Login: '' + @name
    PRINT @tmpstr
    IF (@type IN ( ''G'', ''U''))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = ''    CREATE LOGIN '' + QUOTENAME( @name ) + '' FROM WINDOWS WITH DEFAULT_DATABASE = ['' + @defaultdb + '']''
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ''PasswordHash'' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = ''    CREATE LOGIN '' + QUOTENAME( @name ) + '' WITH PASSWORD = '' + @PWD_string + '' HASHED, SID = '' + @SID_string + '', DEFAULT_DATABASE = ['' + @defaultdb + '']''

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + '', CHECK_POLICY = '' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + '', CHECK_EXPIRATION = '' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + ''; DENY CONNECT SQL TO '' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + ''; REVOKE CONNECT SQL TO '' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + ''; ALTER LOGIN '' + QUOTENAME( @name ) + '' DISABLE''
    END
	set @tmpstr2 = ''IF NOT EXISTS (select * from sys.server_principals where name = '''''' + @name + '''''')''; 
	PRINT @tmpstr2;
	PRINT ''BEGIN''
	PRINT @tmpstr;  -- print create login command
	--Let''s check for server permissions for account
	DECLARE server_perm CURSOR FOR select dp.permission_name from sys.server_permissions dp, sys.server_principals sp
	where dp.grantee_principal_id=sp.principal_id and name = @name;
	OPEN server_perm;
	FETCH server_perm INTO @permission;
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			PRINT ''    GRANT '' + @permission + '' TO '' + quotename(@name);
			FETCH server_perm INTO @permission;
		END
	END
	DEALLOCATE server_perm;
		-- Now let''s check for server roles of those logins
	DECLARE role_cursor CURSOR FOR
		SELECT sp.name FROM sys.server_principals sp, sys.server_role_members srm
		WHERE sp.principal_id=srm.role_principal_id and srm.member_principal_id = 
		(SELECT principal_id FROM sys.server_principals WHERE name = @name);
	OPEN role_cursor;
	FETCH role_cursor INTO @role_name;
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			set @tmpstr = ''    exec sp_addsrvrolemember '' + quotename(@name) + '', '' + quotename(@role_name);
			PRINT @tmpstr;    -- print sp_addrolemember string
		END
		FETCH role_cursor INTO @role_name;
	END
	PRINT ''END''
	CLOSE role_cursor;
	DEALLOCATE role_cursor;
	-- now let''s check whether the system and user databases mapping at least for the open databases fit
	DECLARE open_dbs CURSOR FOR SELECT name FROM sys.databases where is_read_only=0 and state=0;
	OPEN open_dbs;
	FETCH open_dbs into @dbname; 
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			BEGIN
				SET @dname = NULL;
				SET @dschema = NULL
				CREATE TABLE ##temp_user_schema (user_name sysname, schema_name sysname);
				SET @tmpstr = ''SELECT name,  default_schema_name FROM '' + QUOTENAME(@dbname) + ''.sys.database_principals WHERE name = '''''' + QUOTENAME(@name) + '''''''';
				INSERT INTO ##temp_user_schema exec (@tmpstr);
				SELECT @dname=user_name, @dschema=schema_name from ##temp_user_schema;
				DROP TABLE ##temp_user_schema;
				IF @dname IS NOT NULL and @dschema IS NOT NULL
				BEGIN
					PRINT ''IF EXISTS (SELECT name FROM sys.databases where is_read_only=0 and state=0 and name = '''''' + @dbname + '''''')'';
					PRINT ''BEGIN'';
					PRINT ''     DECLARE @str varchar(300); SET @str = ''''USE '' + @dbname + ''''''; EXEC(@str);''
					PRINT ''     IF NOT EXISTS (SELECT name from sys.database_principals WHERE name = '''''' + @dname + '''''')'';
					PRINT ''     BEGIN''
					PRINT ''         CREATE USER '' + quotename(@dname) + '' FROM LOGIN '' + quotename(@name);
					IF @dschema <> ''dbo''
					BEGIN
						PRINT ''         IF NOT EXISTS (SELECT name FROM sys.schemas where name = '''''' + @dschema + '''''' and name <> ''''dbo'''')'';
						PRINT ''         BEGIN'';
						PRINT ''             ALTER USER '' + quotename(@dname) + '' WITH DEFAULT_SCHEMA = '' + quotename(@dschema);
						set @two_ends =''Y''
					-- now we need to fix the database specific roles
					END
					DECLARE db_role_cursor CURSOR FOR
					SELECT sp.name FROM sys.database_principals sp, sys.database_role_members srm
					WHERE sp.principal_id=srm.role_principal_id and srm.member_principal_id = 
					(SELECT principal_id FROM sys.database_principals WHERE name = @dname);
					OPEN db_role_cursor;
					FETCH db_role_cursor INTO @db_role_name;
					WHILE (@@fetch_status <> -1)
					BEGIN
						IF (@@fetch_status <> -2)
						BEGIN
							set @tmpstr = ''            ALTER ROLE '' + quotename(@db_role_name) + '' ADD MEMBER '' + quotename(@dname);
							PRINT @tmpstr;    -- print adding role member string

						END
						FETCH db_role_cursor INTO @db_role_name;
					END
					CLOSE db_role_cursor;
					DEALLOCATE db_role_cursor;
					IF @two_ends = ''Y''
						PRINT ''         END''
					IF @dschema <> ''dbo''
					BEGIN
						PRINT ''         DECLARE @str2 varchar(1000); SET @str = ''''CREATE SCHEMA '' + quotename(@dschema) + '' AUTHORIZATION '' + quotename(@dname) + ''''''; EXEC(@str)''
					END
					PRINT ''     END''						
					set @two_ends = NULL
					PRINT ''END''
					PRINT ''GO''
				END
			END
		END
		FETCH open_dbs into @dbname;
	END
	CLOSE open_dbs;
	DEALLOCATE open_dbs;
	PRINT ''USE [master]'';
	PRINT ''GO'';
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0')