Drop login error

Hi
I am trying to drop a windows login via Management Studio (CTP June) but it comes up with
'Login domain\user' has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)
I cannot see of any permissions that this user has granted. Is there a system view in SQL 2005 which shows permissions this user has granted?
Thanks,
Priyanga

You need to look at the "Security Catalog Views" topic in Books Online. Specifically, take a look at sys.server_permissions and sys.server_principals views to map principal_id of the login you are looking at to the permissions. You can do that at database level as well.

Something along these lines (use this as an idea):

select * from sys.server_permissions
where grantee_principal_id =
(select principal_id from sys.server_principals where name = N'DOMAIN\user')

Alternatively, in Object explorer, you can select Security->Logins->DOMAIN/user, Properties of the login, go Securables and add objects (e.g. server(s)) and this will show you explicit permissions of this login on the object(s). You can revoke there explicit permissions as well.

Then you can also go to the properties of the server in Object Explorer, Permissions and click on the login and click on Effective Permissions. This will show all the permissions for this user based on its role membership and explicitly granted permissions.

HTH,
Boris.

|||

Thanks Boris.

What was causing the issue was that the principal_id (nation\pk0159_a) was linked to grantor_principal_id in the sys.server_permissions as apposed to the grantee_principal_id. The object responsible for this issue was an endpoint. When i dropped the endpoint, i was allowed to drop nation\pk0159_a.

This explains why no objects were showing as being granted permissions by nation\pk0159_a in object explorer in SQL Management Studio.

Cheers,
Priyanga