Saturday, March 28, 2015

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

USE [NORTHWND];
GO
--Create temporary principal
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
GO
CREATE USER user1 FOR LOGIN login1;
GRANT CREATE SCHEMA to user1;
GRANT CREATE TABLE to user1;
GO
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
-- Set the execution context to login1.
EXECUTE AS LOGIN = 'login1';
--Verify the execution context is now login1.
SELECT SUSER_NAME(), USER_NAME();
--Create schema & table
create schema user1;
create table user1.TBL1 (ID INTEGER PRIMARY KEY, Name VARCHAR(200));
GO
SELECT * FROM user1.TBL1;
-- DENY SELECT ON [user1].[TBL1] TO [user1]
-- Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
REVERT;
--Clean up
drop table user1.TBL1;
drop schema user1;
drop user user1;
drop login login1;