Jazz Jazz - 20 days ago 15
SQL Question

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

Today I've been reading about

USER
,
LOGIN
,
CREATE
,
GRANT
, etc, but I'm not getting the logic behind it.

As far as I understand a
LOGIN
is an access to a server level, while an
USER
is an access to a database level. Is this correct? If so, what does really mean?

I was imagining the server as a room with many file cabinets. A
LOGIN
gives me access to the room, but not necessarily to some/all cabinets, while a
USER
gives me access to a definite cabinet. If this analogy is correct, what is the point of, for example, executing the following command

CREATE USER testusername WITHOUT LOGIN


I mean, what is the point of having a user with a 'key' to open some cabinets (key granted by the command
GRANT
) but not giving him access to the room.

Is there a 'master key' which allows an user to have access to all the file cabinets in the room?

Finally I was trying to see how this works, but I'm having some problems.

I tried the following:

CREATE LOGIN testl
WITH PASSWORD = 'test'

CREATE USER test
FOR LOGIN testl


GRANT SELECT, INSERT, UPDATE ON Doctor TO test

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


BEGIN TRANSACTION
EXECUTE AS USER = 'test'
SELECT *
FROM Doctor

INSERT INTO Doctor VALUES ('Johnson', 'Jack',2)

UPDATE Doctor SET Dep_ID=2 WHERE Doc_ID=71

SELECT *
FROM Doctor

REVERT

ROLLBACK

REVOKE SELECT, UPDATE, INSERT ON Doctor FROM test

/*DROP USER test
DROP LOGIN testl*/


All seemed to work fine, but after Rolling back this transaction and trying to revoke the privileges from
Test
I got the following error:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.


Then I tried to Drop the Login and User and I get the following error:

Msg 15151, Level 16, State 1, Line 2
Cannot drop the user 'test', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the login 'testl', because it does not exist or you do not have permission.


What am I doing wrong?

Answer

Your room and cabinet analogy is basically correct. Logins are server (or more accurately SQL instance) level principals whereas users are database level principals.

The user without login could be thought of as a file clerk locked in the room (never enters the room from the outside) and assigned to a single file cabinet. The security context of a user without a login is confined to that database only.

Your "master key" analogy maps to server roles. For example, a login that is a member of the sysadmin server role has full control over all databases on the SQL instance and is automatically mapped to the dbo user in all databases.

The reason for the error after running the script is that you are still running under the context of the test user. You need to execute REVERT; to return to your original security context.