5 thoughts on “sp_SrvPermissions

  1. brenda grossnickle says:

    The second result set does not have column alias names for Drop_Script, Add_Script. They are shown as (No column Name).

    • brenda grossnickle says:
      -- Server level roles
      SET @sql = 
          N'SELECT Logins.name AS UserName, Roles.name AS RoleName, ' + NCHAR(13) + 
          N'   ''EXEC sp_dropsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation + 
                  ','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation + 
                  ','''''''') + '';'' AS Drop_Script, ' + NCHAR(13) + 
          N'   ''EXEC sp_addsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation + 
                  ','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation + 
                  ','''''''') + '';''  AS Add_Script' + NCHAR(13) + 
          N'FROM sys.server_role_members RoleMembers ' + NCHAR(13) + 
          N'JOIN sys.server_principals Logins ' + NCHAR(13) + 
          N'   ON RoleMembers.member_principal_id = Logins.principal_id ' + NCHAR(13) + 
          N'JOIN sys.server_principals Roles ' + NCHAR(13) + 
          N'   ON RoleMembers.role_principal_id = Roles.principal_id ' + NCHAR(13) + 
          N'WHERE 1=1 '
  2. Bahti Samet Coban says:

    First of all, thanks for this amazing script and sharing with us.
    I found your script while I was trying to build the same query and got stuck at one point. I realised I couldn’t get necesssary info with your script too.
    I have a login which has a connect grant for an endpoint. I can query this but I can’t develop a create script since there is no info about the endpoint name in sys.server_permissions table.

    I believe there should be a way for this?

    • Thanks 🙂 I’m rather proud of it myself. It should be easy enough to fix the endpoint part I think. It’s going to be similar to what I’ve got in sp_DBPermissions. sys.server_principals.major_id = sys.endpoints.endpoint_id. I’ve never dealt much with endpoints so I didn’t have any easy way to test it. If you’d like to email me a script to create an endpoint or two and add some permissions I’ll get the script working.

      • Bahti Samet Coban says:

        Hi Kenneth,

        Currently I am working on this script. I successfully added Endpoints, then I realized that server principals are also an issue. I also fixed that. Now I am working on a new class added after SQL 2014 I believe: Availability Groups. I will share with you when it is finished.

Leave a Reply to Bahti Samet Coban Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,530 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: