If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.
Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).
a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.
b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB
Above was the solution to our issues.
Cheers – J
Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx
Other hints:
+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?
+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;
Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp
What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';
Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'
What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'
SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems
Thursday, April 28, 2011
Wednesday, April 27, 2011
Fun with SQL Server DatabaseMail and Service Accounts
If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.
Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).
a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.
b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB
Above was the solution to our issues.
Cheers – J
Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx
Other hints:
+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?
+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;
Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp
What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';
Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'
What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'
SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems
Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).
a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.
b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB
Above was the solution to our issues.
Cheers – J
Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx
Other hints:
+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?
+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;
Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp
What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';
Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'
What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'
SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems
Thursday, April 21, 2011
jQuery ASP.Net Find Value of Input Hidden
http://www.foliotek.com/devblog/extending-jquery-to-select-asp-controls/
jQuery.expr[':'].asp = function(elem, i, match) {
return (elem.id && elem.id.match(match[3] + "$"));
};
alert($(":asp(fy)").val());
fy = $(":asp(h1)").val();
jQuery.expr[':'].asp = function(elem, i, match) {
return (elem.id && elem.id.match(match[3] + "$"));
};
alert($(":asp(fy)").val());
fy = $(":asp(h1)").val();
Tuesday, April 19, 2011
.Net JS - Register Client Script
/// http://msdn.microsoft.com/en-us/library/kx145dw2%28v=VS.90%29.aspx
String csname = "SpreadJS";
String csurl = "Spread_New_or_All.js";
Type cstype = this.GetType();
ClientScriptManager cs = Page.ClientScript;
if (!cs.IsClientScriptIncludeRegistered(cstype, csname))
{
cs.RegisterClientScriptInclude(cstype, csname, ResolveClientUrl(csurl));
}
String csname = "SpreadJS";
String csurl = "Spread_New_or_All.js";
Type cstype = this.GetType();
ClientScriptManager cs = Page.ClientScript;
if (!cs.IsClientScriptIncludeRegistered(cstype, csname))
{
cs.RegisterClientScriptInclude(cstype, csname, ResolveClientUrl(csurl));
}
Thursday, April 7, 2011
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
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
Subscribe to:
Posts (Atom)