Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

script synchronize server permission between two servers

Hello,

I search a script to synchronize or get server permission (example grant view server state) between two servers.

Thanks
0
bibi92
Asked:
bibi92
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please tell us more about this question. Like this we can't do nothing but guess what's going in your mind.
0
 
bibi92Author Commented:
Hello,

I search how to generate Grant All User to view Server State from all users.

Thanks

Regards
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can give that permission to PUBLIC so any user that connect to SQL Server will have that permission:
GRANT VIEW SERVER STATE TO PUBLIC

Open in new window

0
 
Eugene ZCommented:
try

this below script from
http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html


and read \use code for "copy login" additional steps that you may like to have as well

http://www.databasejournal.com/features/mssql/article.php/3922256/Re-generating-SQL-Server-Logins.htm
Re-generating SQL Server Logins

SET NOCOUNT ON 

SELECT  'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context' 

-- Role Members 
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) 
        + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) 
        + QUOTENAME(usr2.name, '''') AS '--Role Memberships' 
FROM    sys.server_principals AS usr1 
        INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id 
        INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id 
ORDER BY rm.role_principal_id ASC 

-- Permissions 
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS 
        + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS 
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS 
        + ']' AS '--Server Level Permissions' 
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK ) 
        INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id 
WHERE   server_principals.type IN ( 'S', 'U', 'G' ) 
ORDER BY server_principals.name, 
        server_permissions.state_desc, 
        server_permissions.permission_name 

Open in new window

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now