May 6, 2011 at 12:49 am
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
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
thanks!
May 9, 2011 at 11:14 am
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