locked
how to replicate permissions on multiple database from one login to another new login RRS feed

  • Question

  • I just want to creat a new windows login in sql server and need to assign all the permissions( in all the databases) from one existed login.

    end of the day i should have two logins(existed and new one) have same permissions on all the existed dbs.

    Kindly help?


    -

    Thursday, February 14, 2013 9:43 AM

Answers

  • SELECT 'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', ' + 
    QUOTENAME(R.name)
    FROM sys.server_principals L JOIN sys.server_role_members RM
    ON L.principal_id=RM.member_principal_id
    JOIN sys.server_principals R
    ON RM.role_principal_id=R.principal_id
    WHERE L.type IN ('U','G','S') AND L.Name ='Loginname'

    AND L.name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND L.name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND L.name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND L.name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM', 'sa');

    That gives you the login for the client.
    ----------------------------information on login mapping name-----------------
    exec master..sp_MSloginmappings 'loginname'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Maggie Luo Sunday, February 24, 2013 3:16 PM
    Thursday, February 14, 2013 9:56 AM
  • There are several ways that permissions can originate. So you have to check multiple places. (If you do this regularly you can automate this by writing a stored procedure.)

    • Server-level permission can come from membership in the fixed server roles or user-defined server roles.
    • Server-level permissions can come from permission grants to logins or user-defined server roles.
    • Database-level permission can come from membership in the fixed database roles or user-defined server roles in each database.
    • Database-level permissions can come from permission grants to users or user-defined database roles in each database.
    • Permissions can be acquired by switching to an application role and providing the password.
    • Permissions can be acquired by executing a stored procedure that includes the EXECUTE AS clause.

    I have placed useful queries to retrieve these permission on the SQL Server wiki at Database
    Engine Effective Permissions

    Remember:
    Everyone belongs to the public role and gets any permission assigned there.
    The guest role (disabled by default) can add permissions if enabled.
    Windows users can be members of Windows groups which can have logins and pick up permissions which SQL Server doesn't explicitly know about. (Because SQL Server doesn't know about Windows group membership until someone connects and provides a login token.)
    A DENY of permissions at any level will override a GRANT. Except for members of the sysadmin fixed server role.
    Members of the local computer administrator group can always elevate their privileges to sysadmin.

    In your case, consider adding these duplicate permissions to server and database roles, so you don't have to do it again when you add another person.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Sunday, February 24, 2013 3:15 PM
    Thursday, February 14, 2013 4:48 PM

All replies

  • SELECT 'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', ' + 
    QUOTENAME(R.name)
    FROM sys.server_principals L JOIN sys.server_role_members RM
    ON L.principal_id=RM.member_principal_id
    JOIN sys.server_principals R
    ON RM.role_principal_id=R.principal_id
    WHERE L.type IN ('U','G','S') AND L.Name ='Loginname'

    AND L.name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND L.name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND L.name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND L.name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM', 'sa');

    That gives you the login for the client.
    ----------------------------information on login mapping name-----------------
    exec master..sp_MSloginmappings 'loginname'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Maggie Luo Sunday, February 24, 2013 3:16 PM
    Thursday, February 14, 2013 9:56 AM
  • There are several ways that permissions can originate. So you have to check multiple places. (If you do this regularly you can automate this by writing a stored procedure.)

    • Server-level permission can come from membership in the fixed server roles or user-defined server roles.
    • Server-level permissions can come from permission grants to logins or user-defined server roles.
    • Database-level permission can come from membership in the fixed database roles or user-defined server roles in each database.
    • Database-level permissions can come from permission grants to users or user-defined database roles in each database.
    • Permissions can be acquired by switching to an application role and providing the password.
    • Permissions can be acquired by executing a stored procedure that includes the EXECUTE AS clause.

    I have placed useful queries to retrieve these permission on the SQL Server wiki at Database
    Engine Effective Permissions

    Remember:
    Everyone belongs to the public role and gets any permission assigned there.
    The guest role (disabled by default) can add permissions if enabled.
    Windows users can be members of Windows groups which can have logins and pick up permissions which SQL Server doesn't explicitly know about. (Because SQL Server doesn't know about Windows group membership until someone connects and provides a login token.)
    A DENY of permissions at any level will override a GRANT. Except for members of the sysadmin fixed server role.
    Members of the local computer administrator group can always elevate their privileges to sysadmin.

    In your case, consider adding these duplicate permissions to server and database roles, so you don't have to do it again when you add another person.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Sunday, February 24, 2013 3:15 PM
    Thursday, February 14, 2013 4:48 PM
  • can you check -http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/758e2485-1def-4549-a4d4-4568b028009d

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, February 15, 2013 2:30 AM