The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

T-SQL Scripts

The title says it all – this page contains my T-SQL Script repository for all things related to Transact-SQL.

Advertisements

14 Responses to “T-SQL Scripts”

  1. siva said

    how we can get the logins & Users who have to the restore permissions on the instance.

  2. Sudarshan Narasimhan said

    Siva – did you mean, how to find out which logins/users have permission to perform restore database on an instance?

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

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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: