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

/*
Creates user, assigns full permissions to specified schema an RO to all other schemas in the DB
*/


USE MASTER

GO

ALTER PROC dbo.dba_Assign_DB_Schema_Permissions (@db VARCHAR (256), @schema VARCHAR (256), @user VARCHAR (256), @isADName INT = 1)
AS
BEGIN

/*****************************************************************************
Joe Kelly
2011-04-07 11:23:12.193

For assigning users very specific permissions. Assumes user is in AD.

If a SQL log-in does not exist it is created. Will not effect existing
sql login.

If the log-in is not a member of the specified DB they are added as guest
and specified DB is set as default. Will not effect (current) DB settings
for an existing user.

Creates access and CRUD for specifiec login for specified DB as guest

Creates SELECT permissions to all other schemas in the specified database

Creates all permissions but Ownership and Take Control for the specified
schema in the specified DB.

Can be parameterized (prime number modulo) for attachment to a UI (checkboxes).

May be paired with a specific dbo.dba_Deny_DB_Schema_Permissions though should
be unnecessary if this is parameterized (i.e. grant select & exec on
another's schema but not alter)

Joe Kelly
2012-02-29 11:02:14.367

Modified for AD and non AD accounts

-- AD
EXEC Master.dbo.dba_Assign_DB_Schema_Permissions 'TARGETDB', 'BUD', 'UserA', 1
-- Non AD
EXEC Master.dbo.dba_Assign_DB_Schema_Permissions 'TARGETDB', 'BUD', 'UserA', 0



------------------------------------------------------------------------------
-- DATABASE level permissions

CONNECT
This grants or denies the ability to enter the database. When a new user is
created, it is granted by default.

CREATE DEFAULT
This grants or denies the ability to create a default. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

CREATE FUNCTION
This grants or denies the ability to create a function. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

CREATE PROCEDURE
This grants or denies the ability to create a stored procedure. This
permission is granted implicitly to the db_ddladmin and db_owner fixed
database roles. In SQL Server 2005 or higher compatibility mode, the user
will still need ALTER SCHEMA rights to create one in a particular schema.

CREATE TABLE
This grants or denies the ability to create a table. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

CREATE VIEW
This grants or denies the ability to create a view. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

VIEW DEFINITION
This grants or denies the ability to view the underlying T-SQL or metadata
on objects within the database. The db_securityadmin database fixed server
role has this permission implicitly.

------------------------------------------------------------------------------

-- SCHEMA level permissions
ALTER
This grants or denies the ability to alter the existing schema.

EXECUTE
This grants or denies the ability to issue the EXECUTE command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

INSERT
This grants or denies the ability to issue the INSERT command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

DELETE
This grants or denies the ability to issue the DELETE command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

UPDATE
This grants or denies the ability to issue the UPDATE command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

SELECT
This grants or denies the ability to issue the SELECT command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

REFERENCES
This grants or denies the ability to create relationships between objects
such as foreign keys on tables referencing other tables or the use of
SCHEMABINDING by views and functions. The permission is granted
implicitly to the db_ddladmin fixed database role.

******************************************************************************/

SET NOCOUNT ON

DECLARE @sql NVARCHAR (2048) = ''
, @domain NVARCHAR (128) = 'DOMAIN\'
, @output VARCHAR (8000) = ''
, @error BIGINT = 0
, @count INT = 0
, @iter BIGINT = 0
, @schemaName VARCHAR (256) = ''
, @rowcount INT = 0

DECLARE @schemas TABLE (iter INT IDENTITY (1, 1), SCHEMANAME NVARCHAR(256))
DECLARE @fullUserName NVARCHAR (128) = '', @NTUserName NVARCHAR (128) = ''

IF(@isADName != 0) SELECT @fullUserName = @domain + @user
ELSE SELECT @fullUserName = @user

SELECT @NTUserName = '[' + @fullUserName + ']'

-------------------------------------------------------------------------------
-- See if the specified DB exists on this server

IF NOT EXISTS (
SELECT name FROM master.dbo.sysdatabases WHERE name = @db)
BEGIN
SET @output += 'Specified database, ' + @db
+ ', does not exist on this server'
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END

-------------------------------------------------------------------------------
-- See if the specified schema exists in this DB

SET @sql = 'SELECT SCHEMA_NAME '
+ ' FROM ' + @db + '.INFORMATION_SCHEMA.SCHEMATA '
+ ' WHERE CATALOG_NAME = ''' + @db + ''''
+ ' AND SCHEMA_NAME = ''' + @schema + ''''
-- SELECT @sql
EXEC sp_executesql @sql
SET @rowcount = @@ROWCOUNT

IF (@rowcount != 1)
BEGIN
SET @output += 'Specified schema, ' + @schema
+ ', does not exist in databae: ' + @db -- + DB_NAME()
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END

-------------------------------------------------------------------------------
-- See if we need to create the login

IF NOT EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @fullUserName)
BEGIN
SET @sql = 'CREATE LOGIN ' + @NTUserName + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + @db
-- SELECT @sql
EXEC sp_executesql @sql
SET @error = @@ERROR

IF (@error != 0)
BEGIN
SET @output += 'Failure creating log-in: ' + @fullUserName
+ CHAR(10) + CHAR(13)
+ 'Username does NOT have to be specified with the domain, UCSFMC is assumed. '
+ CHAR(10) + CHAR(13)
+ 'Correct username format for input: SmithJ'
+ CHAR(10) + CHAR(13)
+ ' Is the user in active directory?'
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'Log-in created: ' + @fullUserName
+ CHAR(10) + CHAR(13)

END

-------------------------------------------------------------------------------
-- See if the user exists in this DB

SET @sql = 'SELECT name '
+ ' FROM ' + @db + '.dbo.sysusers '
+ ' WHERE name = ''' + @fullUserName + ''''
-- SELECT @sql
EXEC sp_executesql @sql
SET @rowcount = @@ROWCOUNT

IF (@rowcount = 0)
BEGIN
SET @sql = 'USE ' + @db + ' CREATE USER ' + @NTUserName + ' FOR LOGIN ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql
SET @error = @@ERROR

IF (@error != 0)
BEGIN
SET @output += 'Failure creating user ' + @fullUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @fullUserName + ' created in database ' + @db
+ CHAR(10) + CHAR(13)
END

-------------------------------------------------------------------------------
-- Assign database permissions

---------------------------------------
-- Assign CONNECT

SET @sql = 'USE ' + @db + ' GRANT CONNECT TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CONNECT to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CONNECT'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE DEFAULT

SET @sql = 'USE ' + @db + ' GRANT CREATE DEFAULT TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE DEFAULT to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE DEFAULT'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE FUNCTION

SET @sql = 'USE ' + @db + ' GRANT CREATE FUNCTION TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE FUNCTION to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE FUNCTION'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE PROCEDURE

SET @sql = 'USE ' + @db + ' GRANT CREATE PROCEDURE TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE PROCEDURE to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE PROCEDURE'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE TABLE

SET @sql = 'USE ' + @db + ' GRANT CREATE TABLE TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE TABLE to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE TABLE'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE VIEW

SET @sql = 'USE ' + @db + ' GRANT CREATE VIEW TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE VIEW to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE VIEW'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign VIEW DEFINITION

SET @sql = 'USE ' + @db + ' GRANT VIEW DEFINITION TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning VIEW DEFINITION to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned VIEW DEFINITION'
+ CHAR(10) + CHAR(13)

-- End DB permissions
---------------------------------------

-------------------------------------------------------------------------------
-- Assign schema permissions

---------------------------------------
-- Assign ALTER

SET @sql = 'USE ' + @db + ' GRANT ALTER ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning ALTER to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned ALTER on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign EXECUTE

SET @sql = 'USE ' + @db + ' GRANT EXECUTE ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning EXECUTE to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned EXECUTE on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign INSERT

SET @sql = 'USE ' + @db + ' GRANT INSERT ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning INSERT to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned INSERT on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign DELETE

SET @sql = 'USE ' + @db + ' GRANT DELETE ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning DELETE to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned DELETE on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign UPDATE

SET @sql = 'USE ' + @db + ' GRANT UPDATE ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning UPDATE to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned UPDATE on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign SELECT

SET @sql = 'USE ' + @db + ' GRANT SELECT ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning SELECT to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned SELECT on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign REFERENCES

SET @sql = 'USE ' + @db + ' GRANT REFERENCES ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning REFERENCES to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned REFERENCES on schema ' + @schema
+ CHAR(10) + CHAR(13)

-- End schema permissions
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- Assign SELECT on the other schemas in this DB

SET @sql = 'SELECT DISTINCT SCHEMA_NAME '
+ 'FROM ' + @db + '.INFORMATION_SCHEMA.SCHEMATA '
+ 'WHERE SCHEMA_NAME != ''' + @schema + ''''
+ 'AND SCHEMA_NAME NOT LIKE ''db_%'''

INSERT @schemas (SCHEMANAME) EXEC sp_executesql @sql
SELECT @count = @@ROWCOUNT
INSERT @schemas (SCHEMANAME) SELECT 'dbo'
SELECT @count += @@ROWCOUNT

WHILE (@iter < @count)
BEGIN

SELECT @schemaName = SCHEMANAME FROM @schemas WHERE iter = @iter + 1

SET @sql = 'USE ' + @db + ' GRANT SELECT ON SCHEMA :: [' + @schemaName + '] TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning SELECT to ' + @NTUserName + ' on schema ' + @schemaName
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' assigned SELECT on schema ' + @schemaName
+ CHAR(10) + CHAR(13)

SET @iter += 1

END

-------------------------------------------------------------------------------

SELECT @output + CHAR(10) + CHAR(13) + 'Routing passed'

RETURN

RaiseError:

SELECT @output + CHAR(10) + CHAR(13) + 'Routing failed'

END