:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • 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.

  • Sep 02 / 2008
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, System Stored Procedures

Check size and disk allocation for a database

Space is an important element to be considered. A DBA has to keep track of space in all aspects and it is good practice to keep an eye on all trends that your DB is going on in case of space. Here are some use full T-SQL commands that will help you in space analysis and tracking.

DBCC showfilestats
This DBCC command will show use full information regarding to data file of current database.

sp_spaceused
This command will provide use full information about total size, used size, unused size, index size and data size of current database.

sp_databases
This use full command will provide you size information of all databases. The size shown is combined size of data and log files.

DBCC CHECKALLOC
This use full DBCC command will give you a very detailed view of disk allocation for current database. You will find every information here about storage allocation.

DBCC CHECKALLOC
sp_helpdb adventureWorks
This use full command will provide value able info about mentioned database. It gives other usefull information along with size of data and log files.

helpdb

  • Aug 27 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions

When you last changed password of sa

Changing the passwords regularly is an important requirement of robust security implementation. And it becomes more important when login is sa. To analyze that when last time you implemented this good practice, use following script.

 
USE Master  
 GO  
 SELECT sid, [name], createdate, updatedate  
 FROM master.dbo.syslogins  
 WHERE [name] = 'sa'  
 GO  

And if specific to SQL Server 2005 then

 
USE Master  
 GO  
 SELECT [name], sid, create_date, modify_date  
 FROM sys.sql_logins  
 WHERE [name] = 'sa'  
 GO  

The script will return name, sid, creation date and update/modification date of all of your logins. As sa account can not be updated/modified except that of password, so update date here will be the date when password of sa was changed. And if no name condition is provided then update date tells you any last any update for other logins.

 
USE Master  
 GO  
 SELECT [name], sid, create_date, modify_date  
 FROM sys.sql_logins  
 GO  
  • 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