none
SQL 2008 - Script Logins/Permissions?

    Question

  • Hi,

    I am restoring a development SQL 2008 database over our production one but I want to keep the logins/permissions the same as they are now.  So first I'm going to restore the database over our production copy, but after that I want to run a script that will generate the users and their permissions so everyone who add access before will have the same access after the database is restored.

    Any help appreciated

    -Westside2008
    Saturday, March 13, 2010 12:20 AM

Answers


  • a)

    Logins are kept in master database so

    To transfer logins between different SQL servers refer to the following MS support KB

    http://support.microsoft.com/kb/918992
    How to transfer the logins and the passwords between instances of SQL Server 2008

    b) To script individual object permission
    Right Click on Database
    Point to tasks
    Select 'Generate Scripts' option
    In the window check the "Script all the Objects" option and Click next
    On next screen make sure that all the options should be "FALSE" except Object Level Permissions is TRUE Click on OK and select the output to new query window and click finish
    Copy and save result into a text file

    now after restore to the prod run the script.
    Saturday, March 13, 2010 1:36 AM

All replies


  • a)

    Logins are kept in master database so

    To transfer logins between different SQL servers refer to the following MS support KB

    http://support.microsoft.com/kb/918992
    How to transfer the logins and the passwords between instances of SQL Server 2008

    b) To script individual object permission
    Right Click on Database
    Point to tasks
    Select 'Generate Scripts' option
    In the window check the "Script all the Objects" option and Click next
    On next screen make sure that all the options should be "FALSE" except Object Level Permissions is TRUE Click on OK and select the output to new query window and click finish
    Copy and save result into a text file

    now after restore to the prod run the script.
    Saturday, March 13, 2010 1:36 AM
  • Hi Chirag,

    I manage the Forums for the SQL Server team here at Microsoft, and I would appreciate it if you would ping me at edhickey at microsoft dot com, I would like to discuss your participation in the Forums.

    Regards,

    Ed Hickey

    Friday, April 09, 2010 8:18 PM
  • Hi,

    You can transfer logins with their password with script as well and with SQL Server Business Intelligence Studio using "Transfer Login Task"

    However if you want to transfer securables as well you can try out the below mentioned script. Pls test it on test environment before shooting it on production.

    #####################################################################################

    DECLARE @DatabaseUserName [sysname]
    SET @DatabaseUserName = 'rs'
    SET NOCOUNT ON
    DECLARE
    @errStatement [varchar](8000),
    @msgStatement [varchar](8000),
    @DatabaseUserID [smallint],
    @ServerUserName [sysname],
    @RoleName [varchar](8000),
    @ObjectID [int],
    @ObjectName [varchar](261)
    SELECT
    @DatabaseUserID = [sysusers].[uid],
    @ServerUserName = [master].[dbo].[syslogins].[loginname]
    FROM [dbo].[sysusers]
    INNER JOIN [master].[dbo].[syslogins]
    ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
    WHERE [sysusers].[name] = @DatabaseUserName
    IF @DatabaseUserID IS NULL
    BEGIN
    SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
    'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
    RAISERROR(@errStatement, 16, 1)
    END
    ELSE
    BEGIN
    SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
    '--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
    '--Created By: ' + SUSER_NAME() + CHAR(13) +
    '--Add User To Database' + CHAR(13) +
    'USE [' + DB_NAME() + ']' + CHAR(13) +
    'EXEC [sp_grantdbaccess]' + CHAR(13) +
    CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
    CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
    'GO' + CHAR(13) +
    '--Add User To Roles'
    PRINT @msgStatement
    DECLARE _sysusers
    CURSOR
    LOCAL
    FORWARD_ONLY
    READ_ONLY
    FOR
    SELECT
    [name]
    FROM [dbo].[sysusers]
    WHERE
    [uid] IN
    (
    SELECT
    [groupuid]
    FROM [dbo].[sysmembers]
    WHERE [memberuid] = @DatabaseUserID
    )
    OPEN _sysusers
    FETCH
    NEXT
    FROM _sysusers
    INTO @RoleName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
    CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
    CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
    PRINT @msgStatement
    FETCH
    NEXT
    FROM _sysusers
    INTO @RoleName
    END
    SET @msgStatement = 'GO' + CHAR(13) +
    '--Set Object Specific Permissions'
    PRINT @msgStatement
    DECLARE _sysobjects
    CURSOR
    LOCAL
    FORWARD_ONLY
    READ_ONLY
    FOR
    SELECT
    DISTINCT([sysobjects].[id]),
    '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
    FROM [dbo].[sysprotects]
    INNER JOIN [dbo].[sysobjects]
    ON [sysprotects].[id] = [sysobjects].[id]
    WHERE [sysprotects].[uid] = @DatabaseUserID
    OPEN _sysobjects
    FETCH
    NEXT
    FROM _sysobjects
    INTO
    @ObjectID,
    @ObjectName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @msgStatement = ''
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
    SET @msgStatement = @msgStatement + 'SELECT,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
    SET @msgStatement = @msgStatement + 'INSERT,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
    SET @msgStatement = @msgStatement + 'UPDATE,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
    SET @msgStatement = @msgStatement + 'DELETE,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
    SET @msgStatement = @msgStatement + 'EXECUTE,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
    SET @msgStatement = @msgStatement + 'REFERENCES,'
    IF LEN(@msgStatement) > 0
    BEGIN
    IF RIGHT(@msgStatement, 1) = ','
    SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
    SET @msgStatement = 'GRANT' + CHAR(13) +
    CHAR(9) + @msgStatement + CHAR(13) +
    CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
    CHAR(9) + 'TO ' + @DatabaseUserName
    PRINT @msgStatement
    END
    SET @msgStatement = ''
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
    SET @msgStatement = @msgStatement + 'SELECT,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
    SET @msgStatement = @msgStatement + 'INSERT,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
    SET @msgStatement = @msgStatement + 'UPDATE,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
    SET @msgStatement = @msgStatement + 'DELETE,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
    SET @msgStatement = @msgStatement + 'EXECUTE,'
    IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
    SET @msgStatement = @msgStatement + 'REFERENCES,'
    IF LEN(@msgStatement) > 0
    BEGIN
    IF RIGHT(@msgStatement, 1) = ','
    SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
    SET @msgStatement = 'DENY' + CHAR(13) +
    CHAR(9) + @msgStatement + CHAR(13) +
    CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
    CHAR(9) + 'TO ' + @DatabaseUserName
    PRINT @msgStatement
    END
    FETCH
    NEXT
    FROM _sysobjects
    INTO
    @ObjectID,
    @ObjectName
    END
    CLOSE _sysobjects
    DEALLOCATE _sysobjects
    PRINT 'GO'
    END
    #################################################################################

     

     


    RS_DBA
    Wednesday, April 21, 2010 11:55 AM
  • You should always credit things you get from someone else. This script was posted by at http://www.sql-server-performance.com/2002/object-permission-scripts/2/
    Thursday, July 12, 2012 11:38 PM
  • Hi Chirag

      I was reading one of your reply in Forum. And i am working on this task so when i follow this steps

    b) To script individual object permission
    Right Click on Database
    Point to tasks
    Select 'Generate Scripts' option
    In the window check the "Script all the Objects" option and Click next
    On next screen make sure that all the options should be "FALSE" except Object Level Permissions is TRUE Click on OK and select the output to new query window and click finish

    On last page Object Level Permissions is FLASE and SCript Login is FALSE to so can you advise how we can change it ti TRUE?

    Thanks


    Ankit Shah SQL Server DBA

    Monday, March 04, 2013 1:11 PM