Microsoft SQL Server - Copy User with Owned Schemas, Memberships, Securables, etc

Question

I am trying to migrate a SQL Server 2012 database to a SQL Server 2008 instance. Please don't ask "why". I have to do it. If there was a way around it, believe me, I wouldn't be doing this.

Anyway, I've got the schema and data transferred. I'm having difficulty transferring the SQL Server logins. Generating CREATE SCRIPTS by right-clicking on the users only gives me a basic CREATE USER statement. I need to maintain all the additional information for the users like Owned Schemas, Memberships, and Securables.

Is there a way to automate this?

I tried using SSIS to migrate the logins and it just gave me non-descriptive errors.

Thanks in advance!

You need to understand that the security is a **two-level** thing - first, on the **server-level**, you have the **logins** - and for each database, a login can have a **user** - so you'll probably have to create both logins for the server, as well as users for the individual databases — marc_s, Oct 12 '15 at 14:55
Sorry. Forgot to mention, the logins already exist at the server level. — PaulyWally, Oct 12 '15 at 15:10
Possible duplicate of [T-SQL copy Logins, Users, roles, permissions etc](http://stackoverflow.com/questions/5983676/t-sql-copy-logins-users-roles-permissions-etc) — Paolo, Oct 12 '15 at 15:33

score 0 · Answer 1 · edited Sep 28 '17 at 14:05

This task is fairly involved, below is a set of stored procedures I wrote to script all users with passwords, role memberships and permissions grants, for one or more databases, by specifying the db name wildcard pattern. It was part of a larger effort that completely automated transferring databases from server to server, some 40k of vb script, with 10 stored procedures and a UDF.

Execute sp_CopyDBSecurity, pass db name as the first parameter, the rest can be ignored. I originally wrote this for SQL Server 2000, then updated for SQL Server 2005, just tested it on SQL Server 2014, seems to work still... amazingly.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_CopyDBSecurity]
   (@SourceDB sysname = NULL,
    @TargetDB sysname = NULL,
    @CommentNTLogins bit = 1,
    @pScriptSingleDB bit = 0, 
    @mode varchar(10) = '',
    @ADDomain varchar(128) = NULL,
    @GetVersion bit = 0)
AS
    DECLARE @version varchar(16), @revised varchar(16)

    SET @version = '1.0.7'
    SET @revised = '2008-10-05'

    IF @GetVersion = 1 
    BEGIN
        SELECT @version AS version, @revised AS revised

        RETURN 0
    END

-- author: Mark J. McGinty
-- revised: 16 Aug 2006
-- revised: 25 Dec 2006
-- revised: 07 Sept 2007
-- compensated for possibility of role members->MemberSID being null
-- changed behavior when both source and target are the same, to allow 
--    a single db to be scripted, without comparing it to a target
-- added exists checks in a few places and excluded system users and roles
--    to prevent errors when scripting single db
-- added option to omit NT accounts and priv grants thereto from generated script
-- replaced hand-maintained sort column on #script 
--    table with clustered identity


SET NOCOUNT ON

SET ARITHABORT ON


Declare @cmt varchar(2)
Declare @ScriptSingleDB bit

Set @ScriptSingleDB = @pScriptSingleDB

if @CommentNTLogins = 1
   Set @cmt = '--'
else
   Set @cmt = ''


if (@SourceDB is null) AND (@TargetDB is null)
begin
   if @mode = 'execute'
   begin
      print '@mode = ''execute'' is illegal unless @SourceDB and @TargetDB are different.  (exiting)'
      return 0
   end
   Set @ScriptSingleDB = 1
   Set @SourceDB = DB_NAME()
   Set @TargetDB = 'tempdb'
   --print 'Scripting the currently selected database'
end

if (@SourceDB is null)
   Set @SourceDB = DB_NAME()
if (@TargetDB is null)
   Set @TargetDB = DB_NAME()

if (@SourceDB = @TargetDB)
begin
   if @mode = 'execute'
   begin
      print '@mode = ''execute'' is illegal unless @SourceDB and @TargetDB are different.  (exiting)'
      return 0
   end
   Set @ScriptSingleDB = 1
   --print 'Scripting database: ' + @SourceDB
end



declare @db sysname
declare @server sysname
declare @sql varchar(8000)
set @db = DB_NAME()
set @server = convert(sysname, SERVERPROPERTY('servername'))

DECLARE @pattern varchar(129)

if @ADDomain IS NULL
   SET @pattern = ''
Else
   SET @pattern = @ADDomain + '%'



Create Table #script (
   script varchar(8000) null,
   [id] int IDENTITY(1,1) primary key clustered
)


SET @sql = '/*
** Script Generated by sp_CopyDbSecurity version ' + @version + ' 
** Source: ' + coalesce(convert(varchar(128), SERVERPROPERTY('servername')), '[null server name]') + '.' + @SourceDB + ' 
** Date:   ' + CONVERT (varchar, GETDATE()) + '
*/
'

INSERT INTO #script (script) VALUES (@sql)  


Set @sql = 'print '' 
* * * * * * * * * * * * * * * *

 Processing ... 

* * * * * * * * * * * * * * * *

'' '

INSERT INTO #script (script) values(@sql)

--Set @sql = 'USE ' + @TargetDB + ' ;  
--go'

--INSERT INTO #script (script) values(@sql)

Create Table #sourceuserlist (
    UserName        sysname        collate database_default Null
   ,LoginName       sysname        collate database_default Null
   ,UID             smallint       Null
   ,SID             varbinary(85)  Null
   ,isntuser        bit            not null
)


Set @Sql = 'USE ' + @SourceDB + '
insert #sourceuserlist 
SELECT 
    usu.name as UserName
    ,coalesce(lo.loginname, usu.name) As LoginName
    ,usu.uid
    ,usu.sid
    ,usu.isntuser | usu.isntgroup
from
    sysusers usu 
    left outer join master.dbo.syslogins lo 
    on usu.sid = lo.sid
where(
    usu.islogin = 1 
    and usu.isaliased = 0 
    and usu.hasdbaccess = 1
    and usu.name NOT IN (''sa'', ''BUILTIN\Administrators'')
)' 

execute(@Sql)


Create Table #targetuserlist
   (
    UserName        sysname        collate database_default Null
   ,LoginName       sysname        collate database_default Null
   ,UID             smallint       Null
   ,SID             varbinary(85)  Null
   ,isntuser        bit            not null
)

Set @Sql = 'USE ' + @TargetDB + '
insert #targetuserlist 
SELECT 
    usu.name as UserName
    ,coalesce(lo.loginname, usu.name) As LoginName
    ,usu.uid
    ,usu.sid
    ,usu.isntuser | usu.isntgroup
from
    sysusers usu 
    left outer join master.dbo.syslogins lo 
    on usu.sid = lo.sid
where(
    usu.islogin = 1 
    and usu.isaliased = 0 
    and usu.hasdbaccess = 1
    and usu.name NOT IN (''sa'',  ''BUILTIN\Administrators'')
)' 



if @ScriptSingleDB = 0
    execute(@Sql)



CREATE TABLE #NTUsers (
   [name] sysname not null
)

insert into #NTUsers ([name]) VALUES ('dbo')

Set @Sql = 'USE ' + @SourceDB + '
insert into #NTUsers ([name])
select [name] from sysusers where ((isntuser | isntgroup) = 1) AND [name] NOT LIKE ''' + @pattern + '''' 

-- this table is used to exclude nt users
--
if (@CommentNTLogins = 1) AND (@ADDomain IS NULL)
    execute(@Sql)



CREATE TABLE #sourceperms (
   [priv] sysname not null,
   [schema] sysname not null,
   [obj] sysname not null,
   [user] sysname not null,
   [AccessGrant] varchar(30) not null,
   [action]  int not null,
   [protecttype]  int not null,
   [sysstat]  int not null,
   [id] int not null
)


Set @Sql = 'USE ' + @SourceDB + '
insert #sourceperms execute sp_resolveperms2'

execute(@Sql)

CREATE TABLE #targetperms (
   [priv] sysname not null,
   [schema] sysname not null,
   [obj] sysname not null,
   [user] sysname not null,
   [AccessGrant] varchar(30) not null,
   [action]  int not null,
   [protecttype]  int not null,
   [sysstat]  int not null,
   [id] int not null
)
Set @Sql = 'USE ' + @TargetDB + '
insert #targetperms execute sp_resolveperms2'

if @ScriptSingleDB = 0
    execute(@Sql)


CREATE TABLE #sourceroles (
   [RoleName] sysname not null,
   [RoleId] int not null,
   [IsAppRole] int not null
)

Set @Sql = 'USE ' + @SourceDB + '
insert #sourceroles execute sp_helprole
'
execute(@Sql)



CREATE TABLE #targetroles (
   [RoleName] sysname not null,
   [RoleId] int not null,
   [IsAppRole] int not null
)

Set @Sql = 'USE ' + @TargetDB + '
insert #targetroles execute sp_helprole
'
if @ScriptSingleDB = 0
    execute(@Sql)


CREATE TABLE #sourcerolemembers (
   [DBRole] sysname not null,
   [MemberName] sysname not null,
   [MemberSID] sysname null
)


-- for reasons unknown, sp_helprolemember does not return
-- members of user-defined db roles, even though the SQL
-- statement below was copied directly from it

Set @Sql = 'USE ' + @SourceDB + '
insert #sourcerolemembers
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
                from sysusers u, sysusers g, sysmembers m
                where   g.uid = m.groupuid
                   and g.issqlrole = 1
                   and u.uid = m.memberuid
                order by 1, 2
'


execute(@Sql)

CREATE TABLE #targetrolemembers (
   [DBRole] sysname not null,
   [MemberName] sysname not null,
   [MemberSID] sysname null
)

Set @Sql = 'USE ' + @TargetDB + '
insert #targetrolemembers
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
                from sysusers u, sysusers g, sysmembers m
                where   g.uid = m.groupuid
                   and g.issqlrole = 1
                   and u.uid = m.memberuid
                order by 1, 2
'

if @ScriptSingleDB = 0
    execute(@Sql)





CREATE TABLE #srvrolemembers (
   [ServerRole] sysname not null,
   [MemberName] sysname not null,
   [MemberSID] sysname null
)

Set @Sql = 'insert #srvrolemembers execute sp_helpsrvrolemember
'
execute(@Sql)

Declare @tmpbuf varchar(140)
Set @tmpbuf = 'DEFAULT'
If @ADDomain IS NOT NULL
    Set @tmpbuf = '''' + @ADDomain + ''''


-- probably should use a cursor here to reduce dependencies
if @ScriptSingleDB = 1
begin

    Create Table #script2 (
       script varchar(8000) null
    )


    declare csr0 CURSOR FOR 
        select 'insert #script2 execute sp_scriptserverlogins  N''' + UserName + ''', ' + convert(varchar(30), @CommentNTLogins) + ', ' + @tmpbuf + ', DEFAULT
        ' FROM #sourceuserlist

    open csr0
    FETCH NEXT FROM csr0 INTO @sql

    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
            EXEC(@sql)
            --print @sql
      END
      FETCH NEXT FROM csr0 INTO @sql
    END
    close csr0

    deallocate csr0

    --exec master..xp_execresultset @sql, @SourceDB

    INSERT INTO #script (script) SELECT [script] FROM #script2
    DROP TABLE #script2
end 
else
begin

    INSERT INTO #script (script)
    SELECT  'if not exists (select * from master.dbo.syslogins where loginname = N''' + LoginName + ''')
        exec sp_addlogin N''' + UserName + '''
    '
    FROM #sourceuserlist WHERE isntuser = 0


    INSERT INTO #script (script)
    SELECT  @cmt + 'if not exists (select * from master.dbo.syslogins where loginname = N''' + LoginName + ''')
       ' + @cmt + 'exec sp_grantlogin N''' + LoginName + '''
    '
    FROM #sourceuserlist WHERE isntuser = 1
end


INSERT INTO #script (script)
SELECT 'if not exists (SELECT *  FROM sysusers WHERE (name = N''' +  UserName + '''))
    exec sp_grantdbaccess N''' + LoginName + ''' , N''' +  UserName + '''
'
FROM #sourceuserlist WHERE LoginName NOT IN (SELECT LoginName FROM
   #targetuserlist) AND (UserName NOT IN (SELECT [name] FROM #NTUsers)) 
   AND ((LoginName LIKE @pattern) OR (isntuser = 0))


/*
SELECT * FROM #sourceuserlist
SELECT * FROM #targetuserlist
SELECT * FROM #NTUsers

return 0

*/


INSERT INTO #script (script)
SELECT '
if not exists (SELECT *  FROM sysusers WHERE (issqlrole = 1) AND (name = N''' + s.RoleName + '''))
    exec sp_addrole N''' + s.RoleName + '''
'
 FROM #sourceroles s LEFT OUTER JOIN #targetroles t
    ON s.RoleName = t.RoleName
    WHERE t.RoleName is null
    AND s.RoleName NOT IN (N'db_accessadmin'
        , N'db_backupoperator'
        , N'db_datareader'
        , N'db_datawriter'
        , N'db_ddladmin'
        , N'db_denydatareader'
        , N'db_denydatawriter'
        , N'db_owner'
        , N'db_securityadmin'
        , N'public')



INSERT INTO #script (script)
SELECT 'exec sp_addrolemember ''' + s.dbrole + ''', ''' +   s.membername + '''
'
   FROM #sourcerolemembers s LEFT OUTER JOIN #targetrolemembers t
   ON s.dbrole = t.dbrole and s.membername = t.membername
   WHERE t.dbrole is null AND (s.membername NOT IN (SELECT [name] FROM #NTUsers))


INSERT INTO #script (script)
SELECT  'if exists (select * from sysobjects where [name] = N''' +  [obj] + ''')
   GRANT ' + [priv] + ' ON [' + [schema] + '].[' + [obj] + ']  TO [' + [user] + ']
'
FROM #sourceperms WHERE (obj NOT LIKE 'dt_%') AND (obj NOT LIKE 'sys%')
 AND ([user] NOT IN (SELECT [NAME] FROM #NTUsers))


INSERT INTO #script (script)
SELECT CASE WHEN CHARINDEX('\',  [MemberName]) > 0 THEN @cmt ELSE '' END + 'EXEC sp_addsrvrolemember ''' + [MemberName] + ''', ''' + [ServerRole] + '''
' AS Script FROM #srvrolemembers WHERE [MemberName] NOT IN ('BUILTIN\Administrators', 'sa') AND [MemberName] IN (SELECT [LoginName] FROM #sourceuserlist)

INSERT INTO #script (script) values(' go
')


-- reduce script column to max length of generated output,
-- to avoid space-padding bloat using osql to write results to file
--
Declare @MaxLen int
Set @MaxLen = (SELECT MAX(DATALENGTH(coalesce(script, ''))) FROM #script) + 1
Set @sql = 'ALTER TABLE #script ALTER COLUMN script varchar(' + CONVERT(varchar, @MaxLen) + ')'

execute(@Sql)



if @mode <> 'execute'
begin
   select top 100 percent script + ' ' FROM (select distinct script, [id]
      from #script) drs order by [id]
end
else
begin
   --set @sql = 'select top 100 percent script FROM (select distinct script, [id] from #script) drs order by [id] '
   --exec master..xp_execresultset @sql, @Targetdb

    declare csr0 CURSOR FOR 
        select top 100 percent script + ' ' FROM (select distinct script, [id]
        from #script) drs order by [id]

    open csr0
    FETCH NEXT FROM csr0 INTO @sql

    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
            EXEC(@sql)
            --print @sql
      END
      FETCH NEXT FROM csr0 INTO @sql
    END
    close csr0

    deallocate csr0

    select top 100 percent script + ' ' FROM (select distinct script, [id]
        from #script) drs order by [id]

end



DROP TABLE #script
DROP TABLE #sourceuserlist
DROP TABLE #targetuserlist
DROP TABLE #sourceperms
DROP TABLE #targetperms
DROP TABLE #sourceroles
DROP TABLE #targetroles

DROP TABLE #sourcerolemembers
DROP TABLE #targetrolemembers
DROP TABLE #srvrolemembers
DROP TABLE #NTUsers



GO

/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 5/19/2017 12:59:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[sp_hexadecimal]
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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




GO

/****** Object:  StoredProcedure [dbo].[sp_ResolvePerms]    Script Date: 5/19/2017 12:59:29 PM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO





CREATE PROCEDURE  [dbo].[sp_ResolvePerms]
(
      @GetVersion bit=0
)
AS

-- Programmer: Mark J. McGinty
-- Purpose: Resolve/list object-level permissions
-- Date: 19 Oct 2004

Declare @version varchar(16), @revised varchar(16), @author varchar(16)
Set @version = '1.0.3'
Set @revised = '2007-09-05'
Set @author = 'Mark J. McGinty'

if @GetVersion = 1 
begin
   SELECT @version AS version, @revised AS revised, @author AS author
   return 0
end

SELECT [object name], [user or role], 
    CASE MAX([select]) WHEN 205 THEN 'allowed' WHEN 206 THEN 'denied' ELSE '' END AS [select], 
    CASE MAX([insert]) WHEN 205 THEN 'allowed' WHEN 206 THEN 'denied' ELSE '' END AS [insert],
    CASE MAX([update]) WHEN 205 THEN 'allowed' WHEN 206 THEN 'denied' ELSE '' END AS [update],
    CASE MAX([delete]) WHEN 205 THEN 'allowed' WHEN 206 THEN 'denied' ELSE '' END AS [delete],
    CASE MAX([execute]) WHEN 205 THEN 'allowed' WHEN 206 THEN 'denied' ELSE '' END AS [execute],
    CASE MAX([DRI])  WHEN 205 THEN 'allowed' WHEN 206 THEN 'denied' ELSE '' END AS [DRI]
FROM (
    SELECT  TOP 100 PERCENT o.name AS [object name], 
        user_name(o.uid) AS [schema], 
        user_name(p.uid) AS [user or role], 
        o.sysstat & 0xf AS [sysstat], 
        p.id, 
        CASE action     WHEN 193 THEN 'select' 
                WHEN 195 THEN 'insert'
                WHEN 196 THEN 'update'
                WHEN 197 THEN 'delete'
                WHEN 224 THEN 'execute'
                WHEN  26 THEN 'DRI'
        END AS PrivilegeDesc,

        CASE action WHEN 193 THEN protecttype ELSE 0 END AS [select],
        CASE action WHEN 195 THEN protecttype ELSE 0 END AS [insert],
        CASE action WHEN 196 THEN protecttype ELSE 0 END AS [update],
        CASE action WHEN 197 THEN protecttype ELSE 0 END AS [delete],
        CASE action WHEN 224 THEN protecttype ELSE 0 END AS [execute],
        CASE action WHEN  26 THEN protecttype ELSE 0 END AS [DRI],
        CASE protecttype WHEN 205 THEN 'allowed' 
                WHEN 206 THEN 'denied'
                ELSE 'unknown' END AS AccessGrant,
        action, 
        protecttype 
    FROM    dbo.sysprotects p, dbo.sysobjects o 
    WHERE o.id = p.id 
        AND p.action IN (193, 195, 196, 197, 224, 26)  
        --AND p.id = 437576597  
    ORDER by 1, 2 
) drs GROUP BY [object name], [user or role]

GO

/****** Object:  NumberedStoredProcedure [dbo].[sp_ResolvePerms2]    Script Date: 5/19/2017 12:59:29 PM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE  [dbo].[sp_ResolvePerms2] AS

SET NOCOUNT ON

SELECT  TOP 100 PERCENT 
    CASE action     WHEN 193 THEN 'SELECT' 
            WHEN 195 THEN 'INSERT'
            WHEN 196 THEN 'UPDATE'
            WHEN 197 THEN 'DELETE'
            WHEN 224 THEN 'EXECUTE'
            WHEN  26 THEN 'REFERENCES'
    END AS priv,
    user_name(o.uid) AS [schema], 
    o.name AS [obj], 
    user_name(p.uid) AS [user], 

    CASE protecttype WHEN 205 THEN 'allowed' 
            WHEN 206 THEN 'denied'
            ELSE 'unknown' END AS AccessGrant,
    action, 
    protecttype, 
    o.sysstat & 0xf AS [sysstat], 
    p.id 
from    dbo.sysprotects p, dbo.sysobjects o 
where o.id = p.id 
    AND protecttype = 205
    and p.action in (193, 195, 196, 197, 224, 26)  
order by 1, 2

GO

/****** Object:  StoredProcedure [dbo].[sp_ScriptServerLogins]    Script Date: 5/19/2017 12:59:29 PM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[sp_ScriptServerLogins] 
(
      @login_name sysname = NULL, 
      @CommentNTLogins bit = 1,
      @ADDomain varchar(128) = NULL,
      @GetVersion bit=0
)
AS

Declare @version varchar(16), @revised varchar(16), @author varchar(16)
Set @version = '1.0.9'
Set @revised = '2008-08-23'
Set @author = 'Mark J. McGinty'

if @GetVersion = 1 
begin
   SELECT @version AS version, @revised AS revised, @author AS author
   return 0
end

-- revised by Mark McGinty
-- 08 April 2007
-- altered cursor sql to make NT logins first in the list
-- added @CommentNTLogins parameter (because NT logins will almost always be useless on the target)
-- changed output from print statements to rows returned by SELECT

-- 21 March 2008
-- created sql 2k5-specific version

-- revised by Mark McGinty
-- 23 August 2008
-- added @ADDomain param
-- (overrides @CommentNTLogins)
SET NOCOUNT ON


CREATE TABLE #slscript (
    script varchar(8000) null,
    id  int IDENTITY(1,1) primary key clustered
)


DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @npwd   sysname
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @defdb  sysname
DECLARE @tmpstr  varchar (1024)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
DECLARE @cmt varchar(2)
DECLARE @pattern varchar(129)

if @ADDomain IS NULL
BEGIN
   SET @pattern = '%'
   if @CommentNTLogins = 1
      set @cmt = '--'
   else
      set @cmt = ''
END
Else
BEGIN
   SET @pattern = @ADDomain + '%'
   set @cmt = ''
END


IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, ((isntuser | isntgroup) * 4) | denylogin As xstatus, password, dbname FROM [master].[sys].[syslogins] 
    WHERE (name NOT IN ( 'sa', 'dbo', 'BUILTIN\Administrators'))
    AND (
        (((isntuser | isntgroup) = 1) AND (name LIKE @pattern))
        OR ((isntuser | isntgroup) = 0)
    )
    ORDER BY (isntuser | isntgroup) DESC
ELSE
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, ((isntuser | isntgroup) * 4) | denylogin As xstatus, password, dbname FROM [master].[sys].[syslogins] 
    WHERE name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @npwd, @defdb
IF (@@fetch_status = -1)
BEGIN
  -- PRINT '-- No login(s) found.'

  --INSERT INTO #slscript (script) VALUES ('print ''-- No login(s) found.''')  
  CLOSE login_curs 
  DEALLOCATE login_curs 

  SELECT ' '
  drop table #slscript
  RETURN 0

END
SET @tmpstr = ' 

' + '--' + ' sp_ScriptServerLogins generated script '  + CONVERT (varchar, GETDATE()) + ' on ' + coalesce(convert(varchar(128), SERVERPROPERTY('servername')), '[null server name]') + ' '

INSERT INTO #slscript (script) VALUES (@tmpstr)  

WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    SET @tmpstr = '-- Login: ' + @name + '
'
    INSERT INTO #slscript (script) VALUES (@tmpstr)  
    INSERT INTO #slscript (script) VALUES ('DECLARE @pwd sysname;')  
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = @cmt + 'EXEC master.sys.sp_denylogin ''' + @name + ''''
        INSERT INTO #slscript (script) VALUES (@tmpstr)  
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = @cmt + 'if not exists (select * from [master].[sys].[syslogins] where loginname = N''' + @name + ''')
' + @cmt + 'begin
' 
        INSERT INTO #slscript (script) VALUES (@tmpstr)  
        SET @tmpstr = @cmt + 'EXEC master.sys.sp_grantlogin ''' + @name + ''''
        INSERT INTO #slscript (script) VALUES (@tmpstr)  
        SET @tmpstr = @cmt + 'EXEC master.sys.sp_defaultdb ''' + @name + ''', ''' + @defdb + '''
' + @cmt + 'end
'
        INSERT INTO #slscript (script) VALUES (@tmpstr)  
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@npwd IS NOT NULL)
      BEGIN -- Non-null password
    Set @binpwd = CONVERT(varbinary(256), @npwd)
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        INSERT INTO #slscript (script) VALUES (@tmpstr)  --PRINT @tmpstr
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'if not exists (select * from master.sys.syslogins where loginname = N''' + @name + ''')
      EXEC master.sys.sp_addlogin ''' + @name + ''', @pwd, ''' + @defdb + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 

    SET @tmpstr = 'print ''

* * * Login not created: ' + @name + ' (password is null)

'''


        -- Null password
    --  EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        --     SET @tmpstr = 'if not exists (select * from master.sys.syslogins where loginname = N''' + @name + ''')
    --  EXEC master.sys.sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      INSERT INTO #slscript (script) VALUES (@tmpstr)   
    END
  END

  INSERT INTO #slscript (script) VALUES ('GO 
')   

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @npwd, @defdb
  END
CLOSE login_curs 
DEALLOCATE login_curs 



-- reduce script column to max length of generated output,
-- to avoid space-padding bloat using osql to write results to file
--
Declare @MaxLen int
Set @MaxLen = (SELECT MAX(DATALENGTH(coalesce(script, ''))) FROM #slscript) + 1
Set @tmpstr = 'ALTER TABLE #slscript ALTER COLUMN script varchar(' + CONVERT(varchar, @MaxLen) + ')'

execute(@tmpstr)


SELECT script + ' '  FROM #slscript


drop table #slscript

RETURN 0

GO

I'm not sure what's more troubling: the fact that SO allows answers to be edited by people other than their authors, or the idea that there are people here with enough free freaking time to alter the content of others? Both are mind numbing. That someone would edit my answer, not for reasons of syntax or typos, but strictly matters of personal preferences is irritating at minimum! These edits are NOT more correct, they aren't even consistent. This cuts my incentive to even read SO, much less contribute, right to the bone. Write your own damn answers, leave mine alone! — Mark McGinty, Sep 29 '17 at 21:59

Microsoft SQL Server - Copy User with Owned Schemas, Memberships, Securables, etc

1 Answers1