Script Database or Application Role

Leave a comment

I was surprised that SSMS does not allow the scripting of user defined database/application roles. Looked around the googlesphere and couldn’t find a script that would produce what my purpose required which is migration. The below script will provide you with the GRANT statement for your secured principles. All that remains, is a CREATE ROLE statement preceding the GRANT statements for the environment you’re migrating too. (CREATE ROLE BLUEBOOKapp [YourRoleName])

SELECT ‘GRANT ‘ + dp.permission_name + ‘ ON ‘ + OBJECT_NAME(major_id) + ‘ TO ‘ + rl.name + CHAR(13) COLLATE Latin1_General_CI_AS

FROM sys.database_permissions dp
JOIN sys.database_principals rl
ON dp.grantee_principal_id = rl.principal_id
WHERE rl.NAME = [YourRoleName]

Example of the T-SQL produced by the script.

Example of the T-SQL produced by the script.

Advertisements

SQL Server Statistics Info

Leave a comment

Great article by none other than the “Scary DBA” himself, Grant Fritchey. This article is all about SQL Server statistics which are now a dark art or path less traveled for the hybrid developer/DBA. Like all of his articles this one is very well written with examples that are easy to digest.

 

SQL Server Statistics Questions We Were Too Shy to Ask 

http://bit.ly/17INCWp

Remove data from data (.mdf) file

Leave a comment

SHRINKDB with the EMPTYFILE argument is a handy command that I for some reason continue to forget about.

MSDN – http://msdn.microsoft.com/en-us/library/ms189493.aspx

EMPTYFILE – “Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.”

DBCC SHRINKFILE (EMPTYFILE)

 

 

Instant File Initialization

Leave a comment

Should be on all DBA’s initial install/setup checklists…

Kimberly Tripp

http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/

 

Paul Randal

http://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/

Seek Predicates (Execution Plans)

Leave a comment

I was researching seek predicate information while working through an execution plan looking to increase query performance. Came across this great article by Craig Freedman that explains seek predicates how they function within single column indexes, multiple column indexes and also how functions muck things up a bit for Index seeks.

Craig Freedman’s SQL Server Blog – Seek Predicates

 

SSRS Subscription emails are not being delivered (SharePoint Integration Mode)

Leave a comment

For the past few days we have had issues with subscriptions not being emailed to our end users. I had not experienced this issue before so I googled and found some posts on Dean Kalanquin’s blog that while helpful did not resolve the issue. They did, however, lead me in the right direction shich is why I posted them below.

Monitoring and Troubleshooting Subscriptions

Troubleshooting Subscriptions: Part II, Using the Reporting Trace Log File

Following the first blog I checked the SQL Agent job and all appeared to be fine according to the job’s history. Next, I looked in to the Events table and the Notifications table. To test the event I grabbed the execution code from the job properties, executed the code then queried the Events table repeatedly until I could see the entry. Crude, yes but I was in a pinch. 🙂

exec [SharePoint_ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=’ed209f73-953a-4fe8-96b6-3569bd2151aa’

SELECT * FROM Events

Following the execution I found that records were indeed being placed into both the Events table and the Notifications table as well. This proved that the event processing service was functioning correctly.

My next step was found in Dean’s second blog that lead me to the Trace Log file.

$:\Program Files\Microsoft SQL Server\MSRS10_50.MNPSRS11\Reporting Services\LogFiles\

There I found the error that was returned . .

Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.

This is when the lightbulb above my head lit up. 🙂  I had run across this issue before and it was permissions related. Below is my checklist that will quide you through fixing this issue without blindly applying sysadmin rights to you Report Server service account. 😉

  • Log in to your SQL Reporting Server server and view the configuration options under SQL Server Configuration manager. Once opened click on the SQL Services node and you will see the login used to run the service.
  • Navigate to the login in SQL Server, this under Security>Logins and add sysadmin permissions in Server Roles.
  • Run the subscription via SharePoint, Native Report Server or manually via execution code or SQL Agent job. If the subscription is sent, then the problem is permissions related. If not, hit the Back button and try another blog. 😀
  • Run the following code that grant execute permissions to several objects in the master and msdb databases. Simply Find/Replace [yourlogin] with your login.

GRANT EXECUTE ON msdb.dbo.sp_help_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_delete_job  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [yourlogin]

  • Add the public server role to the MSDB and Master databases under Login properties>User Mappings
  • In addition to the above you will need to GRANT SELECT permissions on ..

msdb.dbo.sysjobs
msdb.dbo.syscategories

Now all that is left is to remove the sysadmin server role and test your subscriptions again. All should be well, if not . . . well I can’t help you then. 😉

 

 

DBCC UPDATEUSAGE (Error 8989)

Leave a comment

I was greeted at work this morning with a wonderful SQL job failed email. The job in question failed with the below details:

“Executed as user: sqluser. The In-row data USED page count for object “sqltable“, index ID 8, partition ID 2387525817270272, alloc unit ID 2387525817270272 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. [SQLSTATE 42000] (Error 2508) The In-row data RSVD page count for object “sqltable“, index ID 8, partition ID 2387525817270272, alloc unit ID 2387525817270272 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. [SQLSTATE 42000] (Error 2508) CHECKDB found 0 allocation errors and 2 consistency errors in table ‘sqltable2‘ (object ID 2071014459). [SQLSTATE 01000] (Error 8990) CHECKDB found 0 allocation errors and 2 consistency errors in database ‘databasename‘. [SQLSTATE 01000] (Error 8989). The step failed.”

For the above issue I have a very concise answer found by way of msdn Books Online. 🙂

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

For further detail follow the below link to the msdn library for a full description and usage of the management command, DBCC UPDATEUSAGE.

MSDN – DBCC UPDATEUSAGE

Older Entries

%d bloggers like this: