SQL Stuff

Solution To Revoke Permissions Error

Error: Server principal ‘INNChris’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

You’ve run across this error before many times. Yes; it’s annoying, and then even more so when you have to run through the Securable Permissions under the Login Properties to resolve the problem. Why go through the GUI when you can simply run some quick SQL logic.

What it looks like when you query it directly.

use master;
set nocount on

       'account name'             = servprin.name
,      'type'                     = servprin.type_desc
,      'permission'               = servperm.permission_name
,      'state'                    = servperm.state_desc
       sys.server_principals servprin
       join sys.server_permissions servperm
       on  servprin.principal_id = servperm.grantee_principal_id
       servprin.type in ('s', 'u', 'g')
       and servprin.name like '%chris%'
order by
       'account name' asc

Once you can see the explicit permissions that were granted, all you need to do is run the following statement to remove (aka REVOKE) the permissions from the account so you can drop the login.

Lets drop those CONNECT permissions from the account.

revoke connect sql to [innchris] as [sa];

Next; we’ll need to determine what kind of GRANTS that Chris did under his account, then simply remove those granted permissions from those objects. You might want to notate what objects had the GRANT permissions applied to them and simply apply the GRANTS back to them using a more universal account.

declare @grants		varchar(50)
set		@grants		= ( select principal_id from sys.server_principals where name = 'mydomain\myusername' )
select * from sys.server_permissions where grantor_principal_id = @grants

So now we know that Chris granted Connect Permissions to an Endpoint. This is where you need to be careful because even though we can simply revoke the permission; you kinda don’t want to break the connectivity to the Endpoint. The best thing to do is have another account take ownership of the Endpoint, and by doing so all corresponding permissions will simply be transferred to the new account.

First; lets see which endpoint that we are dealing with. So far all we have to go on is the ‘Major_ID’, but not the Endpoint name which we’ll need in order to run the ‘take ownership’ grant statement.

use master;
grant take ownership on endpoint::hadr_endpoint to sa
    with grant option;

Notice the ‘sa’. Unfortunately; you can’t do this. You may feel compelled to use a basic pre-existing account to get through this step, but before you think about using ‘sa’, consider this… It will error out. That’s just the way it’s designed.

All you need do in this case is create another SQL Login. In this case; we’ll be using an SQL Login called EndpointOwner. Nothing special here, just a new account with sa rights. You can add something more complex with more granular rights later. The main goal here is to remove the accounts that need to be removed, and making sure you are giving ownership of the existing endpoint to another account so you don’t cause any problems by revoking the old one.


Categories: SQL Stuff

Leave a 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