August 5, 2008 at 4:03 am
Hey everyone,
I feel like a right wolly for ressorting to this but... I am trying to remove a domain login account from sql (not sql account). I removed database rights and all was fine to drop but i continue to get:
Login 'HERMES\bennetp' has granted one or more permission(s). Revoke the permission(s) before dropping the login. (Microsoft SQL Server, Error: 15173)
I have tried revoking view fefinitions on public but get:
Cannot find the user 'HERMES\bennetp', because it does not exist or you do not have permission.
I know i am missing something and feel so stupid, but help! 🙁
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 5, 2008 at 6:38 am
Hello,
Try running this query to see if the Login still has any Server level permissions:
Select *
From sys.server_permissions
Where grantee_principal_id = (Select principal_id From sys.server_principals Where Name = N'HERMES\bennetp')
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 5, 2008 at 7:49 am
Hi there and thanks for replying.
I have already gone through that step. I ran the same script which I found on an MSDN thread and got a result. I then went to server properties and permissions and then deselected.
After that still couldnt delete and the script now only gives column headings.
Any other ideas?
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 5, 2008 at 8:25 am
Hello,
Sorry if it's a low level question for you, but have you checked there are no DB Mappings left? (In SSMS Object Explorer, select Security, Logins, right click on HERMES\bennetp, PropertIes, User Mapping Tab)
Also in SSMS Object Explorer you can right click on the Server, select Properties, the Permissions tab, then pick HERMES\bennetp from the list of Logins. This gives the chance to view Explicit and Effective Permissions.
Regard,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 5, 2008 at 9:20 am
Its ok, I do find that when you have been chasing your tail, its always good to go back to the basics 'is it turned on? etc'. Unfortunately, yes, I am sure and have checked the user mappings and db users etc. The securitables on the login property are empty, so no effective permissions either.
This all came from a simple request, 'The user has left, remove the account'. I have disabled for now, but wonder how this has happened.
I read somewhere that the user could get 'grantor_principal_id' r ather then 'grantee_principal_id'... When i run:
select * from sys.server_permissions
where grantor_principal_id =
(select principal_id from sys.server_principals where name = N'HERMES\bennetp')
I get:
101SERVER_PRINCIPAL3230266323VW VIEW DEFINITIONDDENY
101SERVER_PRINCIPAL3230291323VW VIEW DEFINITIONGGRANT
101SERVER_PRINCIPAL3230308323VW VIEW DEFINITIONDDENY
101SERVER_PRINCIPAL3230428323VW VIEW DEFINITIONGGRANT
101SERVER_PRINCIPAL3230430323VW VIEW DEFINITIONGGRANT
But am not sure how to interporate or what to do about it. Any clues?
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 6, 2008 at 2:42 am
Good Morning,
Is it possible that the user has in the past been granting Server Level Permissions to other users?
Are the 5 records in your last post all that were returned, or just a sample? If it is just those 5 then you can see which other users "HERMES\bennetp" has granted permission to with the following query:-
Select name From sys.server_principals Where principal_id In (266, 291, 308, 428, 430)
You can the use the Server, Properties, Permissions Tab to check these Logins. Hopefully in the list of Permissions for these users you will see one granted by "HERMES\bennetp". If so, you can then revoke and re-grant it yourself. In doing so you remove the link to "HERMES\bennetp".
After this exercise your "grantor" query should return no records.
It may be that the original error message you have been getting is misleading and that the Login "HERMES\bennetp" can’t be dropped because of the permission it has granted, rather than those it has.
Please let me know how you get on.
Regard,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 6, 2008 at 3:02 am
Hi there and good morning to you too.
Thanks for your reply. Yes thos 5 were the entire result set. I have removed principal_id records 428 and 430 (test accounts which I created), still cannot remove bennetp.
The rusults from your scriptp are :
HERMES\wardni266
HERMES\kaleys291
HERMES\hussaia308
However i have looked in Server -> Properties -> Permissions for thes users and can only see myself in the 'Grantor' column. The only rights (ticks) are things like 'Connect SQL' and 'View Databases' and these were granted by 'sa'.
Damn, I really had a good feeling about that one. Anything else?
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 6, 2008 at 6:23 am
Good Afternoon,
I'm fairly sure that the reason why you can’t delete the Login is because it previously granted permissions to other users. The problem is identifying what permissions "HERMES\bennetp" has granted.
You can try looking the other way around, by executing the following SP for each of the 3 Logins that you found:-
Use master
Go
sp_helprotect @username = [HERMES\hussaia]
The grantor may always appear as "dbo" in the result set, but they could still be the permissions that you are trying to track down.
If you just get one or two records for each user, then try revoking and re-granting the permission to the user, and see if they then drop out of the results of the "grantor" query for "HERMES\bennetp".
Fingers crossed …
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 6, 2008 at 7:28 am
There were no records for two of the users but 'hussaia' has the following:
..HERMES\hussaiadboGrant CONNECT.
Using Server -> Properties -> Permissions I un selected his rights and then set them back under my account but it doesnt seem to have had an effect. How should I revoke.
Getting really miffed now, but really appreciate the help.
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 6, 2008 at 9:10 am
Hello again,
For that particular case I think you would have to drop and re-create the Login (probably not what you want to do).
Unfortunately SQL Server 2005 makes it hard to find the permissions that a Login has granted if the Login was a member of the SysAdmin Server Role. Basically the grantor will always display as "dbo" 🙁
At the bottom of this post is a script (not mine) that may help track down the pesky permissions. Unfortunately it needs to be run on each DB. May be start with master and hope for better luck this time:-
BTW - My guess is the permissions in question are a grant of "view definition", so if all else fails you could always try the following:-
Revoke VIEW DEFINITION To [HERMES\hussaia] Cascade
Regards,
John Marsh
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
SELECT dpm.class_desc as [AccessLevel],
ISNULL(OBJECT_NAME(dpm.major_id), 'N/A')
as [ObjectName], dpr1.name AS [GrantedTo], dpr2.name AS [GrantedBy],
CASE dpm.type
WHEN 'AL' THEN 'ALTER'
WHEN 'ALAK' THEN 'ALTER ANY ASYMMETRIC KEY'
WHEN 'ALAR' THEN 'ALTER ANY APPLICATION ROLE'
WHEN 'ALAS' THEN 'ALTER ANY ASSEMBLY'
WHEN 'ALCF' THEN 'ALTER ANY CERTIFICATE'
WHEN 'ALDS' THEN 'ALTER ANY DATASPACE'
WHEN 'ALED' THEN 'ALTER ANY DATABASE EVENT NOTIFICATION'
WHEN 'ALFT' THEN 'ALTER ANY FULLTEXT CATALOG'
WHEN 'ALMT' THEN 'ALTER ANY MESSAGE TYPE'
WHEN 'ALRL' THEN 'ALTER ANY ROLE'
WHEN 'ALRT' THEN 'ALTER ANY ROUTE'
WHEN 'ALSB' THEN 'ALTER ANY REMOTE SERVICE BINDING'
WHEN 'ALSC' THEN 'ALTER ANY REMOTE SERVICE BINDING'
WHEN 'ALSC' THEN 'ALTER ANY CONTRACT'
WHEN 'ALSK' THEN 'ALTER ANY SYMMETRIC KEY'
WHEN 'ALSM' THEN 'ALTER ANY SCHEMA'
WHEN 'ALSV' THEN 'ALTER ANY SERVICE'
WHEN 'ALTG' THEN 'ALTER ANY DATABASE DDL TRIGGER'
WHEN 'ALUS' THEN 'ALTER ANY USER'
WHEN 'AUTH' THEN 'AUTHENTICATE'
WHEN 'BADB' THEN 'BACKUP DATABASE'
WHEN 'BALO' THEN 'BACKUP LOG'
WHEN 'CL' THEN 'CONTROL'
WHEN 'CO' THEN 'CONNECT'
WHEN 'CORP' THEN 'CONNECT REPLICATION'
WHEN 'CP' THEN 'CHECKPOINT'
WHEN 'CRAG' THEN 'CREATE AGGREGATE'
WHEN 'CRAK' THEN 'CREATE ASYMMETRIC KEY'
WHEN 'CRAS' THEN 'CREATE ASSEMBLY'
WHEN 'CRCF' THEN 'CREATE CERTIFICATE'
WHEN 'CRDB' THEN 'CREATE DATABASE'
WHEN 'CRDF' THEN 'CREATE DEFAULT'
WHEN 'CRED' THEN 'CREATE DATABASE DDL EVENT NOTIFICATION'
WHEN 'CRFN' THEN 'CREATE FUNCTION'
WHEN 'CRFT' THEN 'CREATE FULLTEXT CATALOG'
WHEN 'CRMT' THEN 'CREATE MESSAGE TYPE'
WHEN 'CRPR' THEN 'CREATE PROCEDURE'
WHEN 'CRQU' THEN 'CREATE QUEUE'
WHEN 'CRRL' THEN 'CREATE ROLE'
WHEN 'CRRT' THEN 'CREATE ROUTE'
WHEN 'CRRU' THEN 'CREATE RULE'
WHEN 'CRSB' THEN 'CREATE REMOTE SERVICE BINDING'
WHEN 'CRSC' THEN 'CREATE CONTRACT'
WHEN 'CRSK' THEN 'CREATE SYMMETRIC KEY'
WHEN 'CRSM' THEN 'CREATE SCHEMA'
WHEN 'CRSN' THEN 'CREATE SYNONYM'
WHEN 'CRSV' THEN 'CREATE SERVICE'
WHEN 'CRTB' THEN 'CREATE TABLE'
WHEN 'CRTY' THEN 'CREATE TYPE'
WHEN 'CRVW' THEN 'CREATE VIEW'
WHEN 'CRXS' THEN 'CREATE XML SCHEMA COLLECTION'
WHEN 'DL' THEN 'DELETE'
WHEN 'EX' THEN 'EXECUTE'
WHEN 'IM' THEN 'IMPERSONATE'
WHEN 'IN' THEN 'INSERT'
WHEN 'RC' THEN 'RECEIVE'
WHEN 'RF' THEN 'REFERENCES'
WHEN 'SL' THEN 'SELECT'
WHEN 'SN' THEN 'SEND'
WHEN 'SPLN' THEN 'SHOWPLAN'
WHEN 'SUQN' THEN 'SUBSCRIBE QUERY NOTIFICATIONS'
WHEN 'TO' THEN 'TAKE OWNERSHIP'
WHEN 'UP' THEN 'UPDATE'
WHEN 'VW' THEN 'VIEW DEFINITION'
WHEN 'VWDS' THEN 'VIEW DATABASE STATE'
END As PermissionType
FROM sys.database_permissions dpm INNER JOIN sys.database_principals dpr1
ON dpm.grantee_principal_id = dpr1.principal_id INNER JOIN sys.database_principals dpr2
ON dpm.grantor_principal_id = dpr2.principal_id
WHERE dpr1.principal_id NOT IN (0,1,2,3,4,16384,16385,16386,16387,16388,16389,16390,16391,16392,16393)
www.sql.lu
SQL Server Luxembourg User Group
August 7, 2008 at 8:05 am
Hi there, good afternoon and thanks for your response.... Thing is I ran it as yoou suggested on each db and got no results for the 'bennetp' user.
I am at a complete loss. He was just a contractor anyway, with limited rights!
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 7, 2008 at 7:35 pm
Have you checked whether the user is aliased to dbo on any databases? run sp_helpuser against each database - it will display a list of aliases and, in a separate pane, a list of aliases. If so, run sp_dropalias 'username', 'dbo' (or other schema) then delete the login
maybe worth a try...
August 8, 2008 at 1:44 am
Good Morning,
Did Ivanna Noh's post help?
If not, your statement "He was just a contractor anyway, with limited rights", gave me a clue. May be the view definitions permission that I mentioned, were actually granted on his Login, rather than by himself.
If you run the below query, you will then see a new record in sys.server_permissions with "HERMES\bennetp".as the grantor. And it will look very similar to the existing three records returned by your grantor query:-
USE master;
Grant View Definition On Login::[HERMES\bennetp] To [HERMES\another-login]
GO
You can remove the record just added by running this query:-
Revoke View Definition On Login::[HERMES\bennetp] To [HERMES\another-login] Cascade
If that works, you can remove the remaining three permission the same way (naturally just change the "To" Login).
If you re-run your grantor query (as below). It should now be empty.
select * from sys.server_permissions
where grantor_principal_id =
(select principal_id from sys.server_principals where name = N'HERMES\bennetp')
If so you can try once again to drop "HERMES\bennetp".
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 8, 2008 at 5:19 am
John, Ivanna,
Thanks to both of you for getting back to me.
Ivanna, I tried what you suggested and could not see the user 'bennetp' listed as a 'dbo' for any database. But thanks for the idea.
John, dude, you rock! At first I didnt get the logic of what you were doing but when I granted the view to the user as myself and then ran the third script, and the revoked the view and re-ran third script it clicked. I then just ran the revoke for the user based on the principal_id's we got from one of your earlier posts.
Was then able to delete user!
Anyway, thanks to all your help it sorted! Now I need to create some sort of procedure that others can follow.
Thanks Agaigin. Take care.
Adam.
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 8, 2008 at 6:16 am
Hello Adam,
Thanks for the update. I'm (very) glad to hear that it worked out in the end.
Cheers,
John
www.sql.lu
SQL Server Luxembourg User Group
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply