Microsoft SQL Server Management Studio

User

Add

declare @dbname varchar(255)
declare @yourapp_USER varchar(255)
declare @yourapp_PASS varchar(255)
declare @yourapp_ROLE varchar(255)
declare @source varchar(255)
declare @options varchar(255)
declare @backslash int

/*******************************************************************/
SET @yourapp_USER = '"SQLDB\centreon"'
SET @yourapp_ROLE = 'centreon'
/******************************************************************

PLEASE CHANGE THE ABOVE VALUES ACCORDING TO YOUR REQUIREMENTS

- Example for Windows authentication:
  SET @yourapp_USER = '"[Servername|Domainname]\Username"'
  SET @yourapp_ROLE = 'Rolename'

- Example for SQL Server authentication:
  SET @yourapp_USER = 'Username'
  SET @yourapp_PASS = 'Password'
  SET @yourapp_ROLE = 'Rolename'

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
It is strongly recommended to use Windows authentication. Otherwise
you will get no reliable results for database usage.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

*********** NO NEED TO CHANGE ANYTHING BELOW THIS LINE *************/

SET @options = 'DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=English'
SET @backslash = (SELECT CHARINDEX('\', @yourapp_USER))
IF @backslash > 0
  BEGIN
    SET @source = ' FROM WINDOWS'
    SET @options = ' WITH ' + @options
  END
ELSE
  BEGIN
    SET @source = ''
    SET @options = ' WITH PASSWORD=''' + @yourapp_PASS + ''',' + @options
  END

PRINT 'create user ' + @yourapp_USER
EXEC ('CREATE LOGIN ' + @yourapp_USER + @source + @options)
EXEC ('USE MASTER GRANT VIEW SERVER STATE TO ' + @yourapp_USER)
EXEC ('USE MASTER GRANT ALTER trace TO ' + @yourapp_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobhistory TO ' + @yourapp_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobschedules TO ' + @yourapp_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobs TO ' + @yourapp_USER)
PRINT 'User ' + @yourapp_USER + ' created.'
PRINT ''

declare dblist cursor for
  select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
    fetch next from dblist into @dbname
    while @@fetch_status = 0 begin
      EXEC ('USE [' + @dbname + '] print ''Grant permissions in the db '' + ''"'' + DB_NAME() + ''"''')
      EXEC ('USE [' + @dbname + '] CREATE ROLE ' + @yourapp_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT EXECUTE TO ' + @yourapp_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT VIEW DATABASE STATE TO ' + @yourapp_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT VIEW DEFINITION TO ' + @yourapp_ROLE)
      EXEC ('USE [' + @dbname + '] CREATE USER ' + @yourapp_USER + ' FOR LOGIN ' + @yourapp_USER)
      EXEC ('USE [' + @dbname + '] EXEC sp_addrolemember ' + @yourapp_ROLE + ' , ' + @yourapp_USER)
      EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" granted.''')
      fetch next from dblist into @dbname
    end
close dblist
deallocate dblist

Drop

declare @dbname varchar(255)
declare @app_USER varchar(255)
declare @app_ROLE varchar(255)

SET @app_USER = '"SQLDB\centreon"'
SET @app_ROLE = 'centreon'

declare dblist cursor for
  select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
    fetch next from dblist into @dbname
    while @@fetch_status = 0 begin
      EXEC ('USE [' + @dbname + '] print ''Revoke permissions in the db '' + ''"'' + DB_NAME() + ''"''')
      EXEC ('USE [' + @dbname + '] EXEC sp_droprolemember ' + @app_ROLE + ' , ' + @app_USER)
      EXEC ('USE [' + @dbname + '] DROP USER ' + @app_USER)
      EXEC ('USE [' + @dbname + '] REVOKE VIEW DEFINITION TO ' + @app_ROLE)
      EXEC ('USE [' + @dbname + '] REVOKE VIEW DATABASE STATE TO ' + @app_ROLE)
      EXEC ('USE [' + @dbname + '] REVOKE EXECUTE TO ' + @app_ROLE)
      EXEC ('USE [' + @dbname + '] DROP ROLE ' + @app_ROLE)
      EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" revoked.''')
      fetch next from dblist into @dbname
    end
close dblist
deallocate dblist

PRINT ''
PRINT 'drop user ' + @app_USER
EXEC ('USE MASTER REVOKE VIEW SERVER STATE TO ' + @app_USER)
EXEC ('DROP LOGIN ' + @app_USER)
PRINT 'User ' + @app_USER + ' dropped.'

Explain plan

Summarizing this doc

  • Right click on database
  • Open SQL request editor
  • Paste request
  • Ctrl-L for explain plan

Locks

Request

select cmd,* from sys.sysprocesses where blocked > 0

Blocked column = blocking process spid. Deadly fix

kill spid

Stored procedure

sp_who2