:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Nov 11 / 2008
  • 0
dbDigger, Security and Permissions, T-SQL Scripts

Database roles for SQL Server databases

A login may be member of a Server role or database role. Member of database roles have permissions for that specific SQL Server database. Following script will show that which SQL Server login is member of a particular database role.

 
USE DBName  
 GO  
 SELECT  
 p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role  
 FROM sys.database_role_members roles  
 JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id  
 JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Server roles for SQL Server

A login may be member of a Server role or database role. Member of server roles have permissions for SQL Server instance level tasks.Following script will show that which SQL Server login is member of a particular server role.

select p.name, p.type_desc, pp.name, pp.type_desc  
 from sys.server_role_members roles  
 join sys.server_principals p on roles.member_principal_id = p.principal_id  
 join sys.server_principals pp on roles.role_principal_id = pp.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Who has access to your database

Once a user is logged in to your SQL Server instance, he may or may not have access to your databases. In order to access a database, SQL Server login of that user must be mapped to database user created inside the database. Use following command to list the logins who have access to your databases. I have used sys.database_principals system view for this information.

 
use DBName  
 GO  
 SELECT UserName = dp.name, UserType = dp.type_desc,  
 LoginName = sp.name, LoginType = sp.type_desc  
 FROM sys.database_principals dp  
 JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Who can logIn to your SQL Server instance

Being a DBA you would like to have information related to access at various levels in SQL Server. Primarily the question is that who can login to your SQL Server instance. Use following command to get list of all log ins who are able/disable to log in to your SQL Server instance. Command will query sys.server_principals system view and will get information about windows logins and SQL Server logins.

SELECT name, type_desc, is_disabled
FROM sys.server_principals

Result will be in following form

Access to SQL Server instance

Also you may apply filter on ‘isdisabled‘ and ‘type_desc‘. In above result set login name sa is disabled so it can not be used for login to SQL Server instance.

  • Nov 05 / 2008
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Beginner SQL Server Cheat Sheet for DB Administration

 -- Create PKey constrainton existing column  
 ALTER TABLE TableName  
 ADD CONSTRAINT PKeyName  
 PRIMARY KEY CLUSTERED (PKeyColumn)  
 GO  


 -- Create Fkey Constraint  
 ALTER TABLE FKeyTable ADD CONSTRAINT  
 FKeyName FOREIGN KEY (FKeyColumn)  
 REFERENCES PKeyTable (PKeyColumn)  
 ON UPDATE NO ACTION  
 ON DELETE NO ACTION  
 GO  


 -- Create check constraint  
 ALTER TABLE TableName WITH NOCHECK ADD CONSTRAINT  
 ConstraintName CHECK ( ConstraintExpression)  
 GO  


 -- Create Unique key constraint  
 ALTER TABLE TableName ADD CONSTRAINT  
 UKeyName UNIQUE NONCLUSTERED (col1,col2)  
 GO  


 -- Craete clustered/non clustered index  
 CREATE CLUSTERED INDEX IndexName ON TableName  
 (ColumnsHere) ON [PRIMARY]  
 GO  


 --Retreive data from excel file  
 select [Server], [Orphaned Logins]  
 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  
 'Data Source= 'Path here';Extended Properties=Excel 8.0')...[Sheet1$]  
 order by [Orphaned Logins] asc

  
 -- Info about locks  
 sp_lock  
 SELECT * FROM sys.dm_tran_locks  
 SELECT * FROM sys.dm_exec_requests  


 -- Track active trans on SQL Server 2005 or 2008  
 SELECT * FROM sys.dm_tran_session_transactions  


 ---------List columns in table  
 SELECT Column_Name, Data_Type  
 FROM information_schema.columns  
 WHERE table_name = 'authors'  


 ----Current Connections to a DB  
 SELECT hostname, program_name,loginame,  
 cpu, physical_io, memusage, login_time,  
 last_batch, [status]  
 FROM master.dbo.sysprocesses  
 WHERE db_name(dbid) = 'DBNameHere'  
 ORDER BY hostname  


 -- identify longest open transaction  
 DBCC OPENTRAN('DBNameHere')  
 GO  


 -- get info about object  
 sp_help ObjectNameHere  


 -- Get list of all table valued UDF in DB  
 SELECT name, crdate  
 FROM sys.sysobjects  
 WHERE XTYPE = 'U'  
 --AF = Aggregate function (CLR)  
 --C = CHECK constraint  
 --D = Default or DEFAULT constraint  
 --F = FOREIGN KEY constraint  
 --L = Log  
 --FN = Scalar function  
 --FS = Assembly (CLR) scalar-function  
 --FT = Assembly (CLR) table-valued function  
 --IF = In-lined table-function  
 --IT = Internal table  
 --P = Stored procedure  
 --PC = Assembly (CLR) stored-procedure  
 --PK = PRIMARY KEY constraint (type is K)  
 --RF = Replication filter stored procedure  
 --S = System table  
 --SN = Synonym  
 --SQ = Service queue  
 --TA = Assembly (CLR) DML trigger  
 --TF = Table function  
 --TR = SQL DML Trigger  
 --TT = Table type  
 --U = User table  
 --UQ = UNIQUE constraint (type is K)  
 --V = View  
 --X = Extended stored procedure 
  • Oct 22 / 2008
  • 4
Date and Time, dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Get years, month and days difference togeather between two dates

Suppose you have two dates and you are required to get difference in following form
00 years, 00 months and 00 days.
Normally we may get year, month, days etc alone through
datediff(yy/mm/dd, firstDate, secondDate) function. But in this case we are required to get Year, month and days difference is required together.
So get date in above format use following T-SQL script

 
SELECT '2007-04-25' as [FirstDate], getdate() as [SecondDate],  
 CONVERT(varchar(6),datediff(yy,'2007-04-25',getdate())) +  
 ' Years, '+  
 CONVERT(varchar(4),datediff(mm, '2007-04-25',getdate())%12)+  
 ' Months and '+  
 CONVERT(varchar(4),datediff(dd, '2007-04-25',getdate())%30)+  
 ' Days' as [RequiredDifference]  
 GO  
Consult us to explore the Databases. Contact us