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

Should You Use CHOOSE(): #SQLNewBlogger

By

I was experimenting with CHOOSE() recently after I was asked a question. In a...

T-SQL Tuesday #204 – Are blogs still relevant? (Roundup)

By

I did not expect 27 responses to my T-SQL Tuesday #204 invitation, but it...

Build vs Buy AI: When to adopt, partner, build or buy

By

Should you adopt an off-the-shelf AI tool, partner with a specialist, build in-house, or...

Read the latest Blogs

Forums

Weird problem altering logins with sysadmin account

By Brandie Tarvin

Has anyone ever seen this before? I have sysadmin permission on a server. I...

Switching Schema II

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Switching Schema II

A Practical Approach to Automating SQL Server Email Notifications

By Kunal Rathi

Comments posted to this topic are about the item A Practical Approach to Automating...

Visit the forum

Question of the Day

Switching Schema II

I run this query:

SELECT 
 s.name AS schemaname
 , o.name AS objectname
 FROM sys.objects o
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE o.name = 'Beer'
I get two rows back for the objects, named dbo.beer and etl.beer. Both the tables have identical schemas and are not partitioned. There are 25 rows in dbo.beer and 0 rows in etl.beer. I run this code:
ALTER TABLE dbo.beer switch TO etl.beer
What happens?

See possible answers