Scripts

Technical Article

Proc to script a user role and object permissions

I wanted to be able to prepare a script of User Roles and permissions for objects in a database so I can be prepared for backup and disaster recovery scenarios. I also wanted to be able to audit the permissions for each role.I wrote this stored procedure to do this.  The usage is:Use NorthwindgoDECLARE @RC […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-10-09

995 reads

Technical Article

sp_CompareTblCnts

This stored procedure does a quick table comparison between 2 databases that are supposed to be the same. Tables found on either db but missing from the other are reported as are record count differences for matching tables. Establish a linked-server with appropriate permissions to compare across servers. Compile in master.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-10-09

145 reads

Technical Article

sp_GetUserInfo

Returns database user information as a runnable script for the specified database. The default is master. Great for restoring user access and permissions after a restore to a development server. Run prior to the restore and save the output to an sql file.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-10-09

1,104 reads

Technical Article

Trigger Generator for data audit

This Procedure generates 3 triggers (for Insert, Update & delete). The purpose of these triggers is to keep a SIMPLE audit trail. Create the table and the procedure in the database to which you want to have the auditing facility. To include a table for auditing run the procedure with Table Name as the parameter.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-10-09

1,242 reads

Technical Article

View all settings for all databases on your server

My stored procedure, sp_dbsettings, allows you to view, in a cross-tabular format, all the common options and their values for all the databases on your server.  Optionally, you can pass a database name as an argument if you just want to view the settings for one database (i.e., EXEC sp_dbsettings 'Northwind').  Otherwise it shows info […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-10-09

676 reads

Technical Article

Find and replace column constraints

I submit this script as an example of how one might go about generating 'Alter Table' statements that find and replace specific column constraints. In my case I had to find all occurrences in a production db of suser_name() as a column constraint and replace it with suser_sname(). This script is only an example and […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-10-08

296 reads

Blogs

Dazzling Stuff in Databricks (SQL) – Qualify

By

Once upon a time I had a series about some cool stuff in Snowflake:...

Advice I Like: Overnight Success

By

“Most overnight successes – in fact any significant successes – take at least 5...

A CLR Assembly Broke My Availability Group

By

Sounds scary! Did I get your attention?

Read the latest Blogs

Forums

Why Successful SQL Server Backups Are Not Enough

By Almighty

Comments posted to this topic are about the item Why Successful SQL Server Backups...

Adding Bits II

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Adding Bits II

An Unlimited Token Budget

By Steve Jones - SSC Editor

Comments posted to this topic are about the item An Unlimited Token Budget

Visit the forum

Question of the Day

Adding Bits II

I have a table, dbo.bittest, where I am testing an application response. This has this structure:

CREATE TABLE [dbo].[BitTest]
(
[insertdate] [datetime] NULL CONSTRAINT [DF__BitTest__insertd__300F11AC] DEFAULT (getdate()),
[a] [bit] NULL
) ON [PRIMARY]
GO
My application runs this code:
INSERT dbo.BitTest
(
    insertdate,
    a
)
VALUES
(DEFAULT, true)
GO

See possible answers