Post reply

View server roles

  • May 6, 2011 at 12:49 am

    #389459

    Hi there,

    Does anyone have a script that list all logins/users on a SQL instance with sysadmin rights?

    Regards

    IC

  • May 6, 2011 at 1:11 am

    #1320807

    select Name from sys.syslogins where sysadmin=1;

    I dont know How to give sysadmin rights to users.

    Thanks

  • May 6, 2011 at 1:22 am

    #1320816

    thanks!

  • May 9, 2011 at 11:14 am

    #1321878

    This sort of works. There are two ways to have the equivalent of sysadmin rights as of SQL Server 2005. The first is to be a member of the sysadmin fixed server role. The second is to have CONTROL SERVER rights. Something like the following will list all server level permissions (except the implicit ones tied to the server roles):

    SELECT

    prin.name [Login],

    perm.permission_name,

    perm.state_desc

    FROM sys.server_permissions perm

    JOIN sys.server_principals prin

    ON perm.grantee_principal_id = prin.principal_id

    ORDER BY [Login], permission_name

    You can use this to filter down to just CONTROL SERVER permissions and even UNION ALL with the previous script (with a couple of additional columns) to get all hits.

    K. Brian Kelley
    @kbriankelley

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply