Really - I'm Not a DBA :: Custom DB Permissions: user - schema - db - login Part 2

Example test scripts for "Really - I'm Not a DBA :: Custom DB Permissions: user - schema - db - login Part 1"


USE TargetDB
GO

-- Server: SERVER
-- DB: TargetDB


SET NOCOUNT ON

-- should work
SELECT 'WORK: CREATE TABLE bud.jkTest_UserA'
CREATE TABLE bud.jkTest_UserA (aVC VARCHAR (128), bINT int)
GO

-- should FAIL
SELECT 'FAIL: CREATE TABLE pa.jkTest_UserA'
CREATE TABLE pa.jkTest_UserA (aVC VARCHAR (128), bINT int)
GO

-- should FAIL
SELECT 'FAIL: CREATE TABLE UCSF_SYSTEM.dbo.jkTest_UserA'
CREATE TABLE UCSF_SYSTEM.dbo.jkTest_UserA (aVC VARCHAR (128), bINT int)
GO

-- should work
SELECT 'WORK: INSERT bud.jkTest_UserA'
INSERT bud.jkTest_UserA (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should work
SELECT 'WORK: INSERT bud.jkTest'
INSERT bud.jkTest (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should FAIL
SELECT 'FAIL: INSERT pa.jkTest'
INSERT pa.jkTest (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should FAIL
SELECT 'FAIL: INSERT UCSF_SYSTEM.dbo.jkTest '
INSERT UCSF_SYSTEM.dbo.jkTest (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should work
SELECT 'WORK: SELECT * FROM bud.jkTest '
SELECT * FROM bud.jkTest
GO

-- should work
SELECT 'WORK: SELECT * FROM pa.jkTest '
SELECT * FROM pa.jkTest
GO

-- should work
SELECT 'WORK: SELECT TOP 33 * FROM TARGETDB.dbo.T_Name '
SELECT TOP 33 * FROM TARGETDB.dbo.T_Name
GO