Thursday, April 28, 2011

Fun with DBMail 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

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

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();

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));
}

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