1

On one of our sql 2005 servers we are no longer able to give any users server roles (ex. sysadmin). It appears to be successful both through the UI and through code. But when we check in the db and in the UI the role is unchecked and not shown for the user in the master db. Permissions don't seem to be the issue as we are testing this as system administrator levels of access. There are also no errors in the log.

Any ideas?

Thanks,

Brandon

Joel Coel
  • 12,910
  • 13
  • 61
  • 99

1 Answers1

0

But when we check in the db and in the UI the role is unchecked and not shown for the user in the master db.

Sysadmin is a serverrole and you need to look at the security tab not under master but outside the databases tree/branch. Or you can use the above query to check as well.

    SELECT name FROM sys.server_role_members srm
INNER JOIN sys.server_principals sp
     ON srm.member_principal_id = sp.principal_id
WHERE srm.role_principal_id = (
     SELECT principal_id
     FROM sys.server_principals
     WHERE [Name] = 'sysadmin')
Sankar Reddy
  • 1,374
  • 8
  • 8