:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Mar 17 / 2011
  • 1
dbDigger, Security and Permissions, T-SQL Scripts

Generate permissions for a login in all databases

In one of my previous posts i shared a script to generate all permissions for a database. Couple of days back a blog reader commented on post and asked for a script that may generate permissions for a specific login in all databases. Such script would be helpful for servers with many databases. I have tried to accomplish the task by combining the previously posted script with some other procedures.
Whole script is composed of 6 steps/modules. Let us have a look at 6 steps that together build whole script.

  1. Create a procedure (USP_GeneratePerm) to generate permissions
  2. Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases
  3. Execute USP_CreateInAllDBs with sp_MSForEachDB
  4. Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases
  5. Execute USP_ExecuteInAll
  6. Housekeeping

As we can see in above steps that three stored procedures are created and executed.  Last step would be to drop the created procedures.

  • Prepare your mind that this script may need some modifications according to your scenario. Especially the statements where system/READ ONLY databases are need to excluded from the databases in which our sp would be executed.
  • Script would not be generated for READ ONLY databases. So for READ ONLY databases permissions may be generated separately.
  • Rather than to execute whole script at a time. Execute each of six steps isolated.
Now we would go through the brief description of above mentioned 6 steps

Create a procedure (USP_GeneratePerm) to generate permissions

A stored procedure would be created in master database. Procedure USP_GeneratePerm would be accept two parameters for database name and login. It would actually generate the permission statements.

Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases

The procedure USP_generatePerm is required to be created in all databases other than system and READ ONLY databases. So to complete this requirement we have to create another procedure USP_Create InAllDBs. It would also except two parameters. One is name of database where primary procedure would be created and second is name of primary procedure. In our case primary procedure is  USP_GeneratePerm.

 Execute USP_CreateInAllDBs with sp_MSForEachDB

In third step we would execute USP_CreateInAllDBs in all database by using sp_MSForEachDB system stored procedure. I have exclude the system databases in script.

Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases

No we have our primary SP created in all databases other than ssytem and READ ONLY databases. Now it is required to execute the primary SP in all databases. For this purpose we would create another SP named USP_ExecuteInAll. It would accept one parameter that is login for which script is required to be generated.

Execute USP_ExecuteInAll

Execute the above created USP_ExecuteInAll by providing login as parameter.

Housekeeping

Drop the procedures created in mater and other databases.
According to above descriptions, it is obvious that login for which permissions are required to be generated is provided in step # 5 i.e. Execute USP_ExecuteInAll.
Click here to download the script file. If any one could help to further improve the script and reduce its complexity then you are welcome with due credit on this blog.

  • Jun 09 / 2010
  • 0
Backup and Restore, dbDigger, Encryption, Security and Permissions, T-SQL Scripts

The backup set holds a backup of a database other than the existing ‘*’ database.

Recently i applied password protected backups for my SQL Server databases as explained in my article Using passwords with SQL Server database backup files. Backups and file removal task worked successfully in test environment. Before moving the encrypted backups setup to production systems i just performed a hands on activity to be smooth with restore process of password protected backups.
I created password protected full backup of AdventureWorks with following script to work with restore scenarios.

After creating backup successfully in ‘D:Testing’ directory, i planned it to restore over an existing database AdvTest.

 

It is notable that password protected backups may not be restored through SSMS, so we have to use T-SQL scripts for this purpose.

I got password used for backup by following select statement

select replace(convert(varchar, getdate(), 102),'.','-')+'-MSSQLTips'
GO

Using following script, i tried to restore AdventureWorks backup on existing AdvTest database

Use master
GO
RESTORE DATABASE AdvTest
FROM DISK='D:TestingAdventureWorks-2010-05-31 15-04-53.bak'
WITH MEDIAPASSWORD='2010-05-31-MSSQLTips'
GO

Restoration of password protected backup failed on existing database failed with following error message
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdvTest’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

After some research on issue i came to know a work around. It is required to use WITH REPLACE option for restore of backup on another database with different name. So i modified my script as

Use master
GO

RESTORE DATABASE AdvTest
FROM DISK='<span style="color: #3333ff; font-family: courier new; font-size: small;">D:TestingAdventureWorks-2010-05-31 15-04-53.bak</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">'
WITH REPLACE, MEDIAPASSWORD='</span><span style="color: #3333ff; font-family: courier new; font-size: small;">2010-05-31-MSSQLTips</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">',
MOVE 'AdventureWorks_data' TO 'D:DB FilesAdvTest.mdf',
MOVE 'AdventureWorks_log' TO 'D:DB FilesAdvTest_log.ldf'

Here i have specified the data and log files of source and target. It is important to note that if database is being restored on same database to which it belongs then move option is not required to be specified.
For example if we are required to restore our AdventureWorks backup on AdventureWorks database then following script would work

Use master
GO

RESTORE DATABASE AdventureWorks
FROM DISK=’D:TestingAdventureWorks-2010-05-31 15-04-53.bak‘
WITH REPLACE, MEDIAPASSWORD=’2010-05-31-MSSQLTips‘,
GO

To get logical and physical file names of any database for use in above scripts, use following command

Use AdvTest
GO

sp_helpfile
GO
  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to delete duplicate rows from a table

A DBA may be often required to delete duplicate rows from a SQL Server database table. Several methods can be used keeping in view the resources and exact situation. Here I will create a specific scenario. After going through this scenario, you will be able to remove the duplicate rows in any given scenario on ground.
Run following script to generate duplicate data in a table named “dups” created in any database that you select. Here I will use “pubs”.

 
-- Create table in any database  
 create table Dups  
 (col1 varchar(50),  
 col2 varchar(50),  
 col3 varchar(50))  
 GO  
 -- Populate the table for duplicate rows  
 INSERT INTO dups  
 SELECT 'Val1','val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val1','Val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val6','Val7','Val6'  
 GO  

Table is created and populated with duplicate rows in it now.
Total rows are 9
Distinct rows are 5, which we have to preserve at the end.
Now we have to remove all duplicate rows, leaving behind just 5 distinct rows in three columns.

Execute the following script to complete the operation

 
-- Remove duplicate rows  
 ALTER TABLE dups  
 ADD ser SMALLINT PRIMARY KEY identity(1,1)  
 GO  
 DELETE FROM dups  
 WHERE ser not in (SELECT max(ser) FROM dups  
 GROUP BY col1,col2,col3)  
 GO  

At the end now we have all duplicate rows removed and our table is with an identity column “ser”. You may also drop this column by using

 -- Drop the temporary column created  
 ALTER TABLE dups  
 DROP COLUMN ser  
 GO  

but for that you have to first drop the constraint attached with it. As in my case it is as follows

 -- Drop PK constraint  
 ALTER TABLE dups  
 DROP PK__dups__286302EC  
 GO  

So it is over. You may get include any number of columns in your distinct rows wish list. You may modify the ‘group by’ parameters to meet any number of columns to consider in duplicate row . Query uses the magic of ‘group by.’ Here it is appropriate to mention that “distinct” and “group by” both produce same result and almost same resource utilization. But one of important difference between both is that group by makes you able to apply an aggregate function on each group of data separately. That fact has used here with “max” in our duplicate removal query.

Click here to read an excellent article on this topic by Arshad Ali. It covers different ways to deal with duplicate rows.

  • Jul 20 / 2009
  • 0
Data Modeling and Database Design, Data Types, dbDigger, T-SQL Scripts

Analyze all identity column values in SQL Server database

Identity columns are created to auto generate keys for records. These keys are best suitable for primary keys. If any value crosses the max value for identity column data type then following over flow error may occur, as here in case of tiny int

Msg 8115, Level 16, State 1, Line 5
Arithmetic overflow error converting IDENTITY to data type tinyint.

Here is a script to generate list of all identity columns in database along with their data types and maximum value consumed

 
Use AdventureWorks  
 GO  
 SELECT  
 SCHEMA_NAME( OBJECTPROPERTY( c.OBJECT_ID, 'SCHEMAID' )) AS [SCHEMA NAME],  
 OBJECT_NAME( c.OBJECT_ID ) AS [TABLE NAME], c.NAME AS [COLUMN NAME],  
 t.name as [Column data Type],seed_value,  
 increment_value,last_value as CurrentMaxValue  
 FROM SYS.identity_COLUMNS c inner join sys.types t on c.user_type_id = t.user_type_id  
 WHERE COLUMNPROPERTY(OBJECT_ID, c.NAME, 'IsIdentity') = 1  
 AND last_value IS NOT NULL  
 order by last_Value Desc  
 GO  

Analyze identity columns in SQL Server database
Now you may analyze the list of identity columns. And any column that is near to fully consume its maximum value may easily be tracked here.

  • Jul 02 / 2009
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Triggers

Enable or disable a DML Trigger through T-SQL and SSMS

I have created triggers to track changed data in various fields of several tables. Such triggers work at table level and are attached with table. While performing schedule updates through SQL Server agent jobs, i disable the triggers in the stored procedure that is performing given updates. It prevents the extra usage of triggers and also optimizes the space by skipping entries by such triggers.
Here is syntax to disable and enable trigger1 on table1

-- Disable the trigger  
 ALTER TABLE table1 DISABLE TRIGGER trigger1  
 GO  
 -- Enable the trigger  
 ALTER TABLE table1 ENABLE TRIGGER trigger1  
 GO  

Similarly another syntax may be used to enable and disable our imaginary trigger trigger1 on imaginary table table1 in this case

 
-- Disable the trigger  
 DISABLE TRIGGER trigger1 on table1  
 GO  
 -- Enable the trigger  
 ENABLE TRIGGER trigger1 on table1  
 GO  

Same task may be performed very easily through SSMS. Suppose trigger trigger1 is created on table table1. And we have to disable and enable it through SSMS

  • Open SSMS
  • Go to table on which trigger is defined
  • Open triggers folder under table

Disable and enable trigger through SSMS

  • Right click on trigger and click on Disable.

Disable trigger through SSMS

Confirmation dialog will be shown and icon of trigger will be slightly changed showing the change in trigger status. To enable the trigger through SSMS, again right click on disabled trigger and click Enable.

Enable trigger through SSMS

Again a confirmation dialog will appear and icon of trigger will be changed reflecting the change in status.

  • Jun 26 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

How to disable or enable SQL Server logon trigger

In my previous post we discussed process to implement logon trigger for different purposes. Later some time it may be required to skip the customizations implemented through logon trigger. So for this you may disable the logon trigger rather than to drop it.
If we want to disable the trigger [RestrictSSMSLogIn] that we created during previous post, following is T-SQL command to disable the logon trigger

 
DISABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  

You may use following T-SQL command to enable the logon trigger later at any time

 
ENABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  
Consult us to explore the Databases. Contact us