:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Oct 06 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Get list of all possibble permissions for a SQL Server 2005 object

It is interesting question that how to get all the possible permissions for SQL Server 2005 object. The object may be any table, view, function. Apart from these objects you could even get list of all possible permissions for your database or server.
Format for this T-SQL script is

 
USE DBNameHere  
 GO  
 SELECT *  
 FROM fn_my_permissions('ObjectName', 'type');  
 GO  

In above script provide DB name first. If you want to generate possible permissions for a database or server itself then provide NULL as objectname and write ‘database’ or ‘server’ as type parameter. For example to get possible server level permissions for server itself we will have following syntax

 
SELECT permission_name  
 FROM fn_my_permissions(null, 'server');  
 GO  

Similarly to get possible permissions for AdventureWorks database we have following syntax

 
USE AdventureWorks  
 GO  
 SELECT permission_name  
 FROM fn_my_permissions(null, 'database');  
 GO  

Here are some examples to get list of all possible permissions for SQL Server objects like view, table, database and server.
To get list of all possible permissions for a view [purchasing.object] in AdventureWorks database

 
USE AdventureWorks  
 GO  
 SELECT *  
 FROM fn_my_permissions('purchasing.vVendor', 'object');  
 GO  

Similarly to get list of all possible permissions for a table [production.product] in AdventureWorks database

 
USE AdventureWorks;  
 GO  
 SELECT *  
 FROM fn_my_permissions('production.product', 'object');  
 GO  

Above commands will provide you all permissions/privileges list. you may choose among these as required.

  • Oct 06 / 2008
  • 0
dbDigger, SQL Server Agent scheduled Jobs, System Stored Procedures, T-SQL Scripts

Create new SQL Agent logs archive files without restart in SQL Server 2005

Just like SQL Server Logs Files, SQL Agent logs archive files are also processed during restart of SQL Server agent. A new SQL Agent file is created and oldest one is replaced if maximum count is exceeded. In situations where production servers remain online with critical jobs scheduled, SQL Agent is not restarted for days and even months. So a way is required to process the SQL Agent logs files without restart. Following system stored procedure will do it for you in SQL Server 2005.

 
USE MSDB  
 GO  
 EXEC sp_cycle_agent_errorlog  
 GO  

As a result new logs archive file for SQL Agent logs will be created, just as it would be through restart of SQL agent Service.

  • Oct 06 / 2008
  • 0
dbDigger, System Stored Procedures, T-SQL Scripts, Transaction log files

Recycle SQL Server logs archives without restart

Every time SQL Server restarts, a new error log file is created. If file count is going to be increase the maximum count specified, then oldest file is replaced by new one else just a new file is added. This process helps to manage the size of logs file. But in situations where production servers remain online for days and months without any restart, the logs file may be heavily populated. This causes difficulty while loading the file with more log records for analysis.
So a mechanism is required to process the log files without SQL Server restart. Following system stored procedure will do it for you

USE MASTER  
 EXEC sp_cycle_errorlog  
 GO  

As a result a new logs file will be craeted and oldest will be replaced if maximum count is going to be exceed.

  • Sep 17 / 2008
  • 0
DBA best practices, DBA thoughts, dbDigger, Documentation, T-SQL Scripts

Naming cionventions for SQL Srever user objects

database objects Naming conventions

Following SQL Server objects naming conventions may be used primarily to standardize the DB objects naming structure. Conventions will be applied to Tables, Views, Stored Procedures, Functions and Triggers.

Tables

  • Table name should be descriptive of its content e.g staff, staff_education.
  • No space should be used; under score may be used where necessary.
  • Adding tbl as a prefix should be avoided.
  • Domain of functionally may be reflected in name as for Inventory Management System related tables, we may name as IMS_Wing, IMS_Vendor, IMS_Issue.

Views

  • View name should have VW as prefix. E.g vw_staff_posting, vw_sancstrength
  • View name may also contain names of its base table/tables. E.g vw_staff_staffeducation

Stored Procedure

  • Name of user stored procedure should be suffixed with USP.
  • Stored Procedure name should reflect application to which it belongs. For example for any AD Hoc reporting application we may use USP_AdHoc_GetSummary

User Defined Functions

  • Name of user defined function should have UDF as a prefix.
  • UDF name should reflect the application to which it belongs. For example for any AD Hoc reporting application we may use UDP_AdHoc_calculateSummary

Triggers

  • Trigger name should always have T as prefix.
  • Next to T it should have I, D or U to reflect insert, delete or update event on which it will be invoked. E.g TI_staffLog, TU_staffLog, TD_StaffLog.
  • 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.

Consult us to explore the Databases. Contact us