March 10, 2016 at 7:51 am
I'm trying to delete an individual login from a server but am failing because the login "has granted one or more permission(s).". When I run the below, code, it tells me that I granted an endpoint permission.
Select * from sys.server_permissions
where grantor_principal_id =
(Select principal_id from sys.server_principals where name = N'MyLogin')
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
105 ENDPOINT 65536 0 259 282 CO CONNECT G GRANT
Except this is a new server and I don't remember creating any endpoints. There are a lot of items under "endpoints" in the GUI. How do I figure out what it was I created so that I can remove it?
March 10, 2016 at 8:03 am
Brandie
I'm guessing you've set up mirroring on the server?
SELECT
l.name AS LoginName
, e.name AS EndpointName
FROM sys.server_permissions p
JOIN sys.server_principals l ON p.grantor_principal_id = l.principal_id
JOIN sys.endpoints e ON p.major_id = e.endpoint_id
WHERE p.class_desc = 'ENDPOINT'
John
March 10, 2016 at 8:30 am
John Mitchell-245523 (3/10/2016)
BrandieI'm guessing you've set up mirroring on the server?
SELECTl.name AS LoginName
, e.name AS EndpointName
FROM sys.server_permissions p
JOIN sys.server_principals l ON p.grantor_principal_id = l.principal_id
JOIN sys.endpoints e ON p.major_id = e.endpoint_id
WHERE p.class_desc = 'ENDPOINT'
John
Thank you for that code. It helped me identify the component.
I don't remember setting up mirroring on this server. Unless I was only doing a quick test, then broke the mirror and forgot to delete everything. But still, I just don't remember doing this.
March 10, 2016 at 11:21 am
Grr. Having this issue with another login.
This login does NOT have sysadmin and can't create endpoints. In fact, when I run all the code, I can't find anything that this login granted permission on. Yet I cannot delete it from the server or the database level.
Server level permissions are "public" only. It is a db_owner in the database. How do I find out what permissions it granted in the database?
March 25, 2016 at 12:09 pm
Brandie Tarvin (3/10/2016)
Grr. Having this issue with another login.This login does NOT have sysadmin and can't create endpoints. In fact, when I run all the code, I can't find anything that this login granted permission on. Yet I cannot delete it from the server or the database level.
Server level permissions are "public" only. It is a db_owner in the database. How do I find out what permissions it granted in the database?
Complete error message?
March 25, 2016 at 4:55 pm
It is a db_owner in the database
are you trying to delete the actual owner of the database?
March 28, 2016 at 4:16 am
Lynn Pettis (3/25/2016)
Brandie Tarvin (3/10/2016)
Grr. Having this issue with another login.This login does NOT have sysadmin and can't create endpoints. In fact, when I run all the code, I can't find anything that this login granted permission on. Yet I cannot delete it from the server or the database level.
Server level permissions are "public" only. It is a db_owner in the database. How do I find out what permissions it granted in the database?
Complete error message?
Drop failed for User 'Username'. (Microsoft.SqlServer.Smo)
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The database principal has granted or denied permissions to objects in the database
and cannot be dropped. (Microsoft SQL Server, Error: 15284)
March 28, 2016 at 4:18 am
Jon.Morisi (3/25/2016)
It is a db_owner in the database
are you trying to delete the actual owner of the database?
No. The databases are owned by my user name. I'm trying to drop an application login.
But you bring up a good point. I should check to see who / what owns db_owner.
EDIT: The properties of db_owner says it is owned by dbo, not the application account in question.
March 28, 2016 at 7:40 am
The database principal has granted or denied permissions to objects in the database
and cannot be dropped. (Microsoft SQL Server, Error: 15284)
The part above would lead me to think the account, somewhere, somehow, did what it says.
A quick-and-dirty query to check for any privileges granted / denied via the acct:
use [DBName];
go
select sdpr.name as [GrantorName]
, sdpr.type_desc
, (select name from sys.database_principals where principal_id = sdp.grantee_principal_id) as [GranteeName]
, sdp.class_desc
, sdp.permission_name
, sdp.state_desc
from sys.database_principals as sdpr
inner join sys.database_permissions as sdp
on sdpr.principal_id = sdp.grantor_principal_id
--where sdpr.principal_id = ##
--where sdpr.name = 'Acct name';
Uncomment out one of the WHERE clauses and put in the appropriate name or id to filter things down, see if that maybe finds something.
March 28, 2016 at 7:59 am
jasona.work (3/28/2016)
The database principal has granted or denied permissions to objects in the database
and cannot be dropped. (Microsoft SQL Server, Error: 15284)
The part above would lead me to think the account, somewhere, somehow, did what it says.
I agree with that. The problem is finding what it granted or denied. Nothing I was trying helped me discover that.
jasona.work (3/28/2016)
A quick-and-dirty query to check for any privileges granted / denied via the acct:
use [DBName];go
select sdpr.name as [GrantorName]
, sdpr.type_desc
, (select name from sys.database_principals where principal_id = sdp.grantee_principal_id) as [GranteeName]
, sdp.class_desc
, sdp.permission_name
, sdp.state_desc
from sys.database_principals as sdpr
inner join sys.database_permissions as sdp
on sdpr.principal_id = sdp.grantor_principal_id
--where sdpr.principal_id = ##
--where sdpr.name = 'Acct name';
Uncomment out one of the WHERE clauses and put in the appropriate name or id to filter things down, see if that maybe finds something.
Thank you for the code. As soon as my coworker gets done fiddling with the instance (he's taken it down), I'll see what this brings up for me.
March 28, 2016 at 8:46 am
<headdesk>
IMPERSONATE.
That's the stupid hangup. IMPERSONATE.
GAH.
March 28, 2016 at 8:58 am
Brandie Tarvin (3/28/2016)
<headdesk>IMPERSONATE.
That's the stupid hangup. IMPERSONATE.
GAH.
Could be worse...
You could've not found anything...
😀
Rather annoying though, how difficult it can be to track down things like this, in the UI.
March 28, 2016 at 9:21 am
jasona.work (3/28/2016)
Brandie Tarvin (3/28/2016)
<headdesk>IMPERSONATE.
That's the stupid hangup. IMPERSONATE.
GAH.
Could be worse...
You could've not found anything...
😀
Rather annoying though, how difficult it can be to track down things like this, in the UI.
Finding nothing was exactly what I was worried about. @=)
Thank you for the code. It helped. EDIT: Because you are correct that I couldn't find a darn thing in the UI.
March 28, 2016 at 9:27 am
Brandie Tarvin (3/28/2016)
jasona.work (3/28/2016)
Brandie Tarvin (3/28/2016)
<headdesk>IMPERSONATE.
That's the stupid hangup. IMPERSONATE.
GAH.
Could be worse...
You could've not found anything...
😀
Rather annoying though, how difficult it can be to track down things like this, in the UI.
Finding nothing was exactly what I was worried about. @=)
Thank you for the code. It helped. EDIT: Because you are correct that I couldn't find a darn thing in the UI.
You're quite welcome, glad to have helped!
April 5, 2016 at 5:33 am
jasona.work (3/28/2016)
The database principal has granted or denied permissions to objects in the database
and cannot be dropped. (Microsoft SQL Server, Error: 15284)
The part above would lead me to think the account, somewhere, somehow, did what it says.
A quick-and-dirty query to check for any privileges granted / denied via the acct:
use [DBName];go
select sdpr.name as [GrantorName]
, sdpr.type_desc
, (select name from sys.database_principals where principal_id = sdp.grantee_principal_id) as [GranteeName]
, sdp.class_desc
, sdp.permission_name
, sdp.state_desc
from sys.database_principals as sdpr
inner join sys.database_permissions as sdp
on sdpr.principal_id = sdp.grantor_principal_id
--where sdpr.principal_id = ##
--where sdpr.name = 'Acct name';
Uncomment out one of the WHERE clauses and put in the appropriate name or id to filter things down, see if that maybe finds something.
Jason,
Is this expected output for your code?
GrantorName type_desc GranteeName class_desc permission_name state_desc
MySQLAcct SQL_USER Domain\MyWindowsAcct DATABASE_PRINCIPAL IMPERSONATE GRANT
Domain\MyWindowsAcct WINDOWS_USER MySQLAcct DATABASE_PRINCIPAL IMPERSONATE GRANT
They can't both have granted IMPERSONATE to each other, can they?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply