locked
How to effiectively clone uses's permission in New DB? RRS feed

  • Question

  • There are dozens of logins in Sql serever instance.
    and also the same number users mapping to these logins in the Database A. Someone of them have read permission  and some of them do write or other permissions.
    Now , i created another DB named B, how to clone the permissions from A to B effectively?
    (Eg. user UUU with the read and write permission in A DB then i hope UUU also have the permission read and write in B DB)
    How to handle this request batchly?
    Any suggestion?
    Thanks in advance!


    --Derek D




    Tuesday, December 9, 2008 7:56 AM

Answers

  • I think you are looking for this:

     

    Save Results to text and execute script in database B

    Code Snippet

    create table #temp

    (username varchar(100), ROLENAME varchar(28), loginname varchar(128),defdbname varchar(128), defschemaname varchar(128), userid int , sid varchar(128))

    INSERT INTO #temp

    EXEC sp_helpuser

    SELECT distinct

    + 'CREATE USER '

    + QUOTENAME(USERNAME) + ' FOR LOGIN '

    + QUOTENAME(LOGINNAME) + ' WITH DEFAULT_SCHEMA = '

    + QUOTENAME(DEFSCHEMANAME) + CHAR(10)

    + 'GO' + CHAR(10)

    FROM #temp

    WHERE loginname IS NOT NULL and username not like 'dbo'

    union all

    select

    + 'EXEC sp_addrolemember N'''

    + ROLENAME + ''',''' + USERNAME + '''' + CHAR(10) + CHAR(13)

    + 'GO'

    FROM #temp

    WHERE loginname IS NOT NULL and username not like 'dbo'

    DROP TABLE #TEMP

    Thanks,

    NP

    Tuesday, December 9, 2008 5:44 PM