:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Aug 20 / 2008
  • 4
dbDigger, Monitoring and Analysis, Security and Permissions, System Stored Procedures, T-SQL Scripts

List permissions on SQL Server 2000 objects through sp_helpProtect()

I was required to list all permissions on a DB or table. Although this can be done through EM/SSMS but the analysis of permissions is more efficient through T-SQL than by any other means. I have found some very use full T-SQL stored procedure sp_helpProtect in this regard. It lists users permissions for an object.
General syntax for sp_helpProtect is

 
sp_helprotect [ [ @name = ] 'object_statement' ]  
 [ , [ @username = ] 'security_account' ]  
 [ , [ @grantorname = ] 'grantor' ]  
 [ , [ @permissionarea = ] 'type' ]  

You may use it in following ways

 
--List all user permissions of all Database objects  
 sp_helprotect  
 GO  
 -- List all user permissions of a table  
 sp_helprotect 'tableNme'  
 GO  
 -- List all user permissions of stored procedure  
 EXEC sp_helprotect 'spName'  
 GO  
 -- List all user permissions of sp granted by dbo  
 sp_helprotect 'spName', NULL,dbo  
 GO  
 -- List all Object type user permissions  
 sp_helprotect NULL, NULL,NULL,'o'  
 GO  
 -- List all statement type user permissions  
 sp_helprotect NULL, NULL,NULL,'s'  
 GO  
 -- List all permissions for a user  
 sp_helprotect NULL,'user'  
 GO  

Note: Basically sp_helprotect is for SQL Server 2000.For SQL Server 2005 and later sp_helprotect has no information regarding securables that were introduced in SQL Server 2005. Read here about List the permissions on SQL Server 2005 objects by using sys.database_permissions and fn_builtin_permissions instead.

  • Aug 07 / 2008
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Upgrade SQL Server

change compatibility level of a SQL Server database

I have a database with compatibility level 80 (upgraded from SQL Server 2000 to SQL Server 2005). Today when i tried to disable the trigger through following command

 Use DBNameHere  
 GO  
 DISABLE TRIGGER triggerName  
 ON tablename  
 GO  

Following error occurred

Msg 156, Level 15, State 1, Line 1
 
Incorrect syntax near the keyword ‘TRIGGER’.

It was surprised as the command looked valid and used many times. It was unusual and i had no way but to disable it through SSMS for time being. Later i tried again in a different way and it worked through following command.

 
USE DBNameHere  
 GO  
 ALTER TABLE tableName  
 DISABLE TRIGGER TriggerName  
 GO  

So if you ever get the above mentioned error on enable/disable command then it may be compatibility level issue. Because disable trigger is SQL Server 2005 command. As compatibility level 80 is of SQL Server 2000 so it will not work. If you do not have any problem then you may easily update compatibility level to 90 for upgraded databases.

To change compatibility level of a database to 90 (SQL Server 2005) use following script

 
ALTER DATABASE AdventureWorks  
 SET SINGLE_USER  
 GO  
 EXEC sp_dbcmptlevel AdventureWorks, 90;  
 GO  
 ALTER DATABASE AdventureWorks  
 SET MULTI_USER  
 GO  

Through SSMS you may change the compatibility level by right clicking the database and click properties. Following screen will appear and you may change the level there.

SQL Server dt properties

By the way compatibility levels of SQL Server databases are as followings

* 60 = SQL Server 6.0

* 65 = SQL Server 6.5

* 70 = SQL Server 7.0

* 80 = SQL Server 2000

* 90 = SQL Server 2005

  • Aug 06 / 2008
  • 4
dbDigger, Logins and Users, SET Options, SSMS tips and tricks, T-SQL Scripts

Set SQL Server database to single user mode or multi user mode

Often we require to set the database in to single user mode. And after specified operation we switch to multi user mode. Here is a script to perform the switch operation.
For changing AdventureWorks database to single user mode

 
ALTER DATABASE adventureworks  
 SET SINGLE_USER  
 GO  

and for again to multi user mode

 
ALTER DATABASE adventureworks  
 SET MULTI_USER  
 GO  

Also you have to consider that if some users are connected to that database then their connections will be dropped without any warning. And you can not set the system databases master, tempDB, msDB to single user mode. For that purpose you have to start your SQL Server in single user mode.
You can also set a database to single user mode through GUI. For SSMS i will go through for steps

  • Right click on database to be set user mode
  • Go to Options in left panel
  • In right panel you will found ‘Restrict Access’ option
  • Choose single user mode
  • Click OK

Set single user mode through SSMS

Same procedure would be repeated to set the database back to multiple user mode

  • Aug 06 / 2008
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts, T-SQL Tips and Tricks

Search for a column in all tables of a database

Today i read an interesting question on a blog, that how to search for a column in all tables of a database. Following script was given by author to search for columns with name like employeeID in adventureworks database.

USE AdventureWorks  
 GO  
 SELECT t.name AS table_name,  
 SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME,  
 c.name AS column_name  
 FROM sys.tables AS t  
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID  
 WHERE c.name LIKE '%EmployeeID%'  
 ORDER BY SCHEMA_NAME, table_name  

It served the purpose but i have a more simple approach for same purpose. My script generates same result in a more simple and flexible way

 
USE AdventureWorks  
 GO  
 SELECT Table_Schema, Table_Name, Column_Name, Data_Type  
 FROM information_schema.columns  
 WHERE table_name in ( select name from adventureworks..sysobjects  
 where xtype = 'U' )  
 and column_name like '%EmployeeID%'  
 order by table_schema, table_name  

So both may be used as convenient.

  • Jul 29 / 2008
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures, T-SQL Scripts

Column names and datatypes in a table

I was required to generate a list of column names along with their data types in a table. For information about a table i used sp_help.

 
USE DBNameHere  
 GO  
 sp_help tableName  
 GO  

And for more to the point result with any filtration required i used following script

Use DBNameHere
GO
SELECT Table_Schema,Table_Name,Column_Name,Data_Type
FROM information_schema.columns
WHERE table_name = ‘TableNameHere’
GO

This produced exact that i wanted.

Consult us to explore the Databases. Contact us