T-SQL Scripts
The title says it all – this page contains my T-SQL Script repository for all things related to Transact-SQL.
The SQL Dude!
On-Disk & In-Memory Pages that teach you the SQL way of life!
The SQL DUDE
Recent Posts
- Azure Automation – How to get your 1st Runbook working properly…
- Scan, Find & Understand Missing Indexes in your SQL Database
- Seek & Evaluate Unused Indexes in your SQL database
- Checklist for SQL Server Database Audit & Security Hardening
- Seek & Understand Top Indexes by Size in your SQL database
- Seek & Destroy Duplicate Indexes in your SQL Database
- SQL Server 2016 – General Availability Date Announced
- Quick SQL Server Info – Script
- theSQLDude – Back from Down Under..
- A scenario on Query Tuning–Included Columns are a good thing!
Feed theSQLDude
Vistor’s Location (Eye of Sauron)
TroubleshootingSql- Addressing SQL Server and TDE with AKV errors
- Upcoming sessions at Microsoft Ignite and PASS Summit
- SQL PASS Summit 2017
- SQL Saturday 686–Upgrade your SQL Server like a Ninja
- Managed PostgreSQL and MySQL for Azure
- Data Platform Summit 2017
- SQL Saturday 613: Building 1 million predictions per second with R-services and SQL Server 2016
- Book on Azure and SQL Server
- Upgrading a Replication Topology to SQL Server 2016
- Introducing VDC_Complete for Backup and Restore applications using SQLVDI
- SQLServerFAQ
- An error has occurred; the feed is probably down. Try again later.
- SQL Server CSS Engineers
- An error has occurred; the feed is probably down. Try again later.
Vistor’s Location (Gollum’s New Eye)
The SQL Dude! 
siva said
how we can get the logins & Users who have to the restore permissions on the instance.
Sudarshan Narasimhan said
Siva – did you mean, how to find out which logins/users have permission to perform restore database on an instance?
siva said
yes sudarshan Thanks you for your reply..once a week i will follow your blog really nice one i will learn alote of new things for your end….. can you please help on this.. on of my database restoration was happend for this reason application check points changed, we need to find out who have the accesss to restore and is there any way to find out who has take restore for that db xx date..
Sudarshan Narasimhan said
Hi Siva,
For a user to perform a Restore database operation he/she needs to have any of the following permissions.
a) sysadmin
b) dbcreator
c) dbo or part of db_owner role (this doesnt apply if the database being restored doesn’t exist or corrupted)
Basically to restore a database, the minimum permission required is CREATE DATABASE permission at the server level (this is granted by the dbcreator server role). You can use the following queries to check which logins have permission to perform restore database.
— all of these logins can run restore database
a) EXEC sp_helpsrvrolemember ‘dbcreator’
b) select * from sys.server_permissions where type = ‘CRDB’
c) select name, sysadmin from sys.syslogins where sysadmin=1
d) Query to pull out members of db_owner role for each database. All of these logins can perform restore database
set nocount on
go
Create table #TmpTableSec1 (Database_name sysname, DB_Role sysname, Member_Name sysname)
DECLARE DBCURSOR CURSOR FOR
select name
from sys.databases where state=0 and name not in (‘tempdb’,’model’,’master’,’msdb’)
Declare @name varchar(50)
Declare @cmd varchar(500)
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = ‘use ‘+ @name +’
select db_name() as DatabaseName, DbRole = g.name, MemberName = u.name
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.name = ”db_owner”
and g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
and u.name ”dbo” order by 1, 2′
INSERT #TmpTableSec1 exec (@cmd)
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
go
select * from #TmpTableSec1 order by Database_name asc
drop table #TmpTableSec1
go
set nocount off
go
Sudarshan Narasimhan said
Siva – I didn’t read your whole comment before. It is possible to find out who performed the database restore. You can find this information stored from MSDB database table restorehistory. Here is the query
select user_name, restore_date, restore_type , replace as [WITH REPLACE?] from msdb..restorehistory
where destination_database_name = ‘yourDBName’
order by restore_date desc
siva said
Hi Sudarshan,
Thanks you very much.
Sudarshan Narasimhan said
Thanks Siva. Also, I didn’t read your whole comment before. It is possible to find out who performed the database restore. You can find this information stored from MSDB database table restorehistory. Here is the query
select user_name, restore_date, restore_type , replace as [WITH REPLACE?] from msdb..restorehistory
where destination_database_name = ‘yourDBName’
order by restore_date desc
siva said
Thanks sudarshan
but we are using light speed tool to taking for backup. from here is there any chage to get who did this mistake.
Sudarshan Narasimhan said
If you’re sure its not a regular restore operation (verified from the msdb..restorehistory table), i’m sure Quest litespeed has logging of its own. Maybe you can check the backup software history since this would be a VDI Restore. From SQL Server side, if you have the errorlogs/application event logs from the time period, you should be able to find out the restore operation being logged there.
siva said
Hi Sudarshan,
Few Days back i have installed SQL 2012 in my system, i have to learn all Security things in 2012.
How can i improve in Security side becuase i m bit week here, please help me some url with good stuff i need to start from today onwards.
I will also attend SQL Clinic in Microsoft Blore, in Jan 7 th but i did not meet you, i wil attend AMITH Session on Memory… its really good, from amith blog only i found your url..
I thought your blogs are completly in Security in sql only ..
Thanks in advance
Sudarshan Narasimhan said
Hi Siva,
I was present @ the SQLClinic but towards the end only. My blogs are on all features in SQL Server. My last few posts have been on security as i worked on some interesting case studies on security recently.
The best place to start with security in SQL Server is to read the following whitepaper which clearly explains the security model starting with SQL 2005 onwards
http://download.microsoft.com/download/1/2/A/12ABE102-4427-4335-B989-5DA579A4D29D/SQL_Server_2008_R2_Security_Best_Practice_Whitepaper.docx
You can also watch this video on SQL 2012 security here on Channel9
http://channel9.msdn.com/posts/SQL-Server-Security
siva said
i want all logins and groups in a instance in sql server 2008 R2
can you please provide script for this
i can use sp_help logins, sys.syslogins, it is correct or not.
for groups what should i do.
Sudarshan Narasimhan said
Siva – each login is present in sys.syslogins be it a group, windows login or SQL login. You have columns like isntgroup and isntuser which will tell you if its a regular user or group.
select name as [LoginName], isntgroup as [Group], isntuser as [WindowsLogin], sysadmin from sys.syslogins
siva said
Hi Sudarshan,
last few days facing one prouble in logshipping.
PROB : Log Shipping Jobs are not Alerting us to failures, even logshipping is not writting any application logs? Please help me if with your sigguestions?
this was in production issue please suggest me asp..plzz………………
Thanks
siva