Execute Select Statement for multiple Database in MSSQL

DECLARE @rn INT = 1, @dbname varchar(MAX) = '';

WHILE @dbname IS NOT NULL

BEGIN

SET @dbname = (SELECT name FROM (SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn

FROM sys.databases WHERE name NOT IN('master','tempdb')) t WHERE rn = @rn);

IF @dbname <> '' AND @dbname IS NOT NULL

EXECUTE ('use '+ @dbname+';

/* Your script code here */

SELECT

DB_NAME() AS DatabaseName,p.name AS ExtendedPropertyName, p.value AS ExtendedPropertyValue

FROM sys.extended_properties AS p

WHERE p.major_id=0 AND p.minor_id=0 AND p.class=0

ORDER BY [Name] ASC;

');

SET @rn = @rn + 1;

END;

Transfer logins and passwords between instances of SQL Server: sp_help_revlogin script

Transfer logins and passwords to destination server (Server B) using scripts generated on source server (Server A)

Step 1:

Create stored procedures that will help generate necessary scripts to transfer logins and their passwords. To do so, connect to Server A using SQL Server Management Studio (SSMS) or any other client tool and run the following script:

USE [master]

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE [dbo].[sp_hexadecimal]

(

@binvalue varbinary(256),

@hexvalue varchar (514) OUTPUT

)

BEGIN

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

END

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

CREATE PROCEDURE [dbo].[sp_help_revlogin]

(

@login_name sysname = NULL

)

BEGIN

DECLARE @name SYSNAME

DECLARE @type VARCHAR (1)

DECLARE @hasaccess INT

DECLARE @denylogin INT

DECLARE @is_disabled INT

DECLARE @PWD_varbinary VARBINARY (256)

DECLARE @PWD_string VARCHAR (514)

DECLARE @SID_varbinary VARBINARY (85)

DECLARE @SID_string VARCHAR (514)

DECLARE @tmpstr VARCHAR (1024)

DECLARE @is_policy_checked VARCHAR (3)

DECLARE @is_expiration_checked VARCHAR (3)

Declare @Prefix VARCHAR(255)

DECLARE @defaultdb SYSNAME

DECLARE @defaultlanguage SYSNAME

DECLARE @tmpstrRole VARCHAR (1024)

IF (@login_name IS NULL)

BEGIN

DECLARE login_curs CURSOR

FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name

FROM sys.server_principals p

LEFT JOIN sys.syslogins l ON ( l.name = p.name )

WHERE p.type IN ( 'S', 'G', 'U' )

AND p.name <> 'sa'

ORDER BY p.name

END

ELSE

DECLARE login_curs CURSOR

FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name

FROM sys.server_principals p

LEFT JOIN sys.syslogins l ON ( l.name = p.name )

WHERE p.type IN ( 'S', 'G', 'U' )

AND p.name = @login_name

ORDER BY p.name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')

BEGIN'

Print @tmpstr

IF (@type IN ( 'G', 'U'))

BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'

END

ELSE

BEGIN -- SQL Server authentication

-- obtain password and sid

SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state

SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

FROM sys.sql_logins

WHERE name = @name

SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

FROM sys.sql_logins

WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = '

+ @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'

IF ( @is_policy_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

END

IF ( @is_expiration_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

END

IF (@denylogin = 1)

BEGIN -- login is denied access

SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

ELSE IF (@hasaccess = 0)

BEGIN -- login exists but does not have access

SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

END

IF (@is_disabled = 1)

BEGIN -- login is disabled

SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

END

SET @Prefix = '

EXEC master.dbo.sp_addsrvrolemember @loginame='''

SET @tmpstrRole=''

SELECT @tmpstrRole = @tmpstrRole

+ CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin''' ELSE '' END

+ CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin''' ELSE '' END

+ CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin''' ELSE '' END

+ CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin''' ELSE '' END

+ CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin''' ELSE '' END

+ CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin''' ELSE '' END

+ CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator''' ELSE '' END

+ CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' ELSE '' END

FROM (

SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],

sysadmin,

securityadmin,

serveradmin,

setupadmin,

processadmin,

diskadmin,

dbcreator,

bulkadmin

FROM sys.syslogins

WHERE ( sysadmin<>0

OR securityadmin<>0

OR serveradmin<>0

OR setupadmin <>0

OR processadmin <>0

OR diskadmin<>0

OR dbcreator<>0

OR bulkadmin<>0

)

AND name=@name

) L

PRINT @tmpstr

PRINT @tmpstrRole

PRINT 'END'

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

END

Step 2: In the SSMS query editor, select the Results to Text option.

Step 3: Run the following statement in the same or a new query window:

EXEC sp_help_revlogin

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

Steps on the destination server (Server B)

Connect to Server B using any client tool (like SSMS) and then run the script generated in Step 4 (output of sp_helprevlogin) from Server A.

How to Check Monthly Growth of Database in SQL Server (Database Growth :last 6 month database grow)

**********************************************************

Below is the script I have come up with database growth of database. It will give the last 6 month database growth for all database of particular instance.

DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate();  -- Include in the statistic all backups from today
SET @months = 6;           -- back to the last 6 months.

;WITH HIST AS
   (SELECT BS.database_name AS DatabaseName
          ,YEAR(BS.backup_start_date) * 100
           + MONTH(BS.backup_start_date) AS YearMonth
          ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
          ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
          ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
    FROM msdb.dbo.backupset as BS
         INNER JOIN
         msdb.dbo.backupfile AS BF
             ON BS.backup_set_id = BF.backup_set_id
    WHERE NOT BS.database_name IN
              ('master', 'msdb', 'model', 'tempdb')
          AND BF.file_type = 'D'
          AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
    GROUP BY BS.database_name
            ,YEAR(BS.backup_start_date)
            ,MONTH(BS.backup_start_date))
SELECT MAIN.DatabaseName
      ,MAIN.YearMonth
      ,MAIN.MinSizeMB
      ,MAIN.MaxSizeMB
      ,MAIN.AvgSizeMB
      ,MAIN.AvgSizeMB
       - (SELECT TOP 1 SUB.AvgSizeMB
          FROM HIST AS SUB
          WHERE SUB.DatabaseName = MAIN.DatabaseName
                AND SUB.YearMonth < MAIN.YearMonth
          ORDER BY SUB.YearMonth DESC) AS GrowthMB
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
        ,MAIN.YearMonth

--SECTION 1 BEGIN

WITH BackupsSize AS(
SELECT TOP 1000
      rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
    , [Year]  = DATEPART(year,[backup_start_date])
    , [Month] = DATEPART(month,[backup_start_date])
    , [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
    , [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM 
    msdb.dbo.backupset
WHERE 
    [database_name] = N'XXXX'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
GROUP BY 
    [database_name]
    , DATEPART(yyyy,[backup_start_date])
    , DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month]) 
--SECTION 1 END
 
--SECTION 2 BEGIN
SELECT 
   b.Year,
   b.Month,
   b.[Backup Size GB],
   0 AS deltaNormal,
   b.[Compressed Backup Size GB],
   0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT 
   b.Year,
   b.Month,
   b.[Backup Size GB],
   b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
   b.[Compressed Backup Size GB],
   b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
   SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
   FROM BackupsSize bs
   WHERE bs.rn = b.rn - 1
) AS d
--SECTION 2 END

Method:2

Windows: How do I log out of a remote desktop that only gives a disconnect option?
(Log off a disconnected user remotely)

remotely logoff vdi connection

Remotely logoff a user by username in one command:
logoff /server:"servername" | qwinsta /server:"servername" "username"

Query that returns list of all Stored Procedures in an MS SQL database

Query that returns list of all Stored Procedures Script in an MS SQL database

use [[DB]
go

declare @aQuery nvarchar(1024);
declare @spName nvarchar(64);
declare allSP cursor for
select p.name from sys.procedures p where p.type_desc = 'SQL_STORED_PROCEDURE' order by p.name;
open allSP;
fetch next from allSP into @spName;
while (@@FETCH_STATUS = 0)
begin
set @aQuery = 'sp_helptext [dbo.' + @spName + ']';
exec sp_executesql @aQuery;
fetch next from allSP;
end;
close allSP;
deallocate allSP;

SQL USERS Completed Reference - Part2

SQL USERS Completed Reference - Part1

Method:8 SQL Server : List Database Users, Roles and permissions through roles

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
INSERT @DB_USers
EXEC sp_MSforeachdb
'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT dbname,username ,logintype ,create_date ,modify_date ,
STUFF(
(
SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2 WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH('')
)
,1,1,'') AS Permissions_user
FROM @DB_USers user1
GROUP BY dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username

SQL USERS Completed Reference -Part1

SQL USERS Completed Reference - Part1

SQL Server query to find list of all users in a databaseSQL Server query to find all permissions/access for all users in a database

SQL USERS Completed Reference -Part2

This Stored Procedure will provide a list of permissions that a user has either applied directly to the user account, or through roles under all databases

Eg: EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;

USE [master] ;

IF EXISTS

(

SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')

AND [type] in (N'P',N'PC')

)

BEGIN

DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;

END

CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns

SET NOCOUNT ON

;

BEGIN TRY

IF EXISTS

(

SELECT * FROM tempdb.dbo.sysobjects

WHERE id = object_id(N'[tempdb].dbo.[#permission]')

)

DROP TABLE #permission

;

IF EXISTS

(

SELECT * FROM tempdb.dbo.sysobjects

WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')

)

DROP TABLE #userroles_kk

;

IF EXISTS

(

SELECT * FROM tempdb.dbo.sysobjects

WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')

)

DROP TABLE #rolemember_kk

;

IF EXISTS

(

SELECT * FROM tempdb.dbo.sysobjects

WHERE id = object_id(N'[tempdb].dbo.[##db_name]')

)

DROP TABLE ##db_name

;

DECLARE

@db_name VARCHAR(255)

,@sql_text VARCHAR(MAX)

;

SET @sql_text =

'CREATE TABLE ##db_name

(

LoginUserName VARCHAR(MAX)

;

DECLARE cursDBs CURSOR FOR

SELECT [name]

FROM sys.databases

ORDER BY [name]

;

OPEN cursDBs

;

FETCH NEXT FROM cursDBs INTO @db_name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql_text =

@sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)

FETCH NEXT FROM cursDBs INTO @db_name

END

CLOSE cursDBs

;

SET @sql_text =

@sql_text + 'IsSysAdminLogin CHAR(1)

,IsEmptyRow CHAR(1)

--PRINT @sql_text

EXEC (@sql_text)

;

DEALLOCATE cursDBs

;

DECLARE

@RoleName VARCHAR(255)

,@UserName VARCHAR(255)

;

CREATE TABLE #permission

(

LoginUserName VARCHAR(255)

,databasename VARCHAR(255)

,[role] VARCHAR(255)

)

;

DECLARE cursSysSrvPrinName CURSOR FOR

SELECT [name]

FROM sys.server_principals

WHERE

[type] IN ( 'S', 'U', 'G' )

AND principal_id > 4

AND [name] NOT LIKE '##%'

ORDER BY [name]

;

OPEN cursSysSrvPrinName

;

FETCH NEXT FROM cursSysSrvPrinName INTO @UserName

WHILE @@FETCH_STATUS = 0

BEGIN

CREATE TABLE #userroles_kk

(

databasename VARCHAR(255)

,[role] VARCHAR(255)

)

;

CREATE TABLE #rolemember_kk

(

dbrole VARCHAR(255)

,membername VARCHAR(255)

,membersid VARBINARY(2048)

)

;

DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR

SELECT [name]

FROM sys.databases

ORDER BY [name]

;

OPEN cursDatabases

;

DECLARE

@DBN VARCHAR(255)

,@sqlText NVARCHAR(4000)

;

FETCH NEXT FROM cursDatabases INTO @DBN

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlText =

N'USE ' + QUOTENAME(@DBN) + ';

TRUNCATE TABLE #RoleMember_kk

INSERT INTO #RoleMember_kk

EXEC sp_helprolemember

INSERT INTO #UserRoles_kk

(DatabaseName,[Role])

SELECT db_name(),dbRole

FROM #RoleMember_kk

WHERE MemberName = ''' + @UserName + '''

--PRINT @sqlText ;

EXEC sp_executesql @sqlText ;

FETCH NEXT FROM cursDatabases INTO @DBN

END

CLOSE cursDatabases

;

DEALLOCATE cursDatabases

;

INSERT INTO #permission

SELECT

@UserName 'user'

,b.name

,u.[role]

FROM

sys.sysdatabases b

LEFT JOIN

#userroles_kk u

ON QUOTENAME(u.databasename) = QUOTENAME(b.name)

ORDER BY 1

;

DROP TABLE #userroles_kk

;

DROP TABLE #rolemember_kk

;

FETCH NEXT FROM cursSysSrvPrinName INTO @UserName

END

CLOSE cursSysSrvPrinName

;

DEALLOCATE cursSysSrvPrinName

;

TRUNCATE TABLE ##db_name

;

DECLARE

@d1 VARCHAR(MAX)

,@d2 VARCHAR(MAX)

,@d3 VARCHAR(MAX)

,@ss VARCHAR(MAX)

;

DECLARE cursPermisTable CURSOR FOR

SELECT * FROM #permission

ORDER BY 2 DESC

;

OPEN cursPermisTable

;

FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3

WHILE @@FETCH_STATUS = 0

BEGIN

IF NOT EXISTS

(

SELECT 1 FROM ##db_name WHERE LoginUserName = @d1

)

BEGIN

SET @ss =

'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')'

EXEC (@ss)

;

SET @ss =

'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''''

EXEC (@ss)

;

END

ELSE

BEGIN

DECLARE

@var NVARCHAR(MAX)

,@ParmDefinition NVARCHAR(MAX)

,@var1 NVARCHAR(MAX)

;

SET @var =

N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''

;

SET @ParmDefinition =

N'@var1 NVARCHAR(600) OUTPUT '

;

EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT

;

SET @var1 =

ISNULL(@var1, ' ')

;

SET @var =

' UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + ''' '

;

EXEC (@var)

;

END

FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3

END

CLOSE cursPermisTable

;

DEALLOCATE cursPermisTable

;

UPDATE ##db_name SET

IsSysAdminLogin = 'Y'

FROM

##db_name TT

INNER JOIN

dbo.syslogins SL

ON TT.LoginUserName = SL.[name]

WHERE

SL.sysadmin = 1

;

DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR

SELECT [name]

FROM tempdb.sys.columns

WHERE

OBJECT_ID = OBJECT_ID('tempdb..##db_name')

AND [name] NOT IN ('LoginUserName','IsEmptyRow')

ORDER BY [name]

;

OPEN cursDNamesAsColumns

;

DECLARE

@ColN VARCHAR(255)

,@tSQLText NVARCHAR(4000)

;

FETCH NEXT FROM cursDNamesAsColumns INTO @ColN

WHILE @@FETCH_STATUS = 0

BEGIN

SET @tSQLText =

N'UPDATE ##db_name SET

IsEmptyRow = ''N''

WHERE IsEmptyRow IS NULL

AND ' + QUOTENAME(@ColN) + ' IS NOT NULL

;

--PRINT @tSQLText ;

EXEC sp_executesql @tSQLText ;

FETCH NEXT FROM cursDNamesAsColumns INTO @ColN

END

CLOSE cursDNamesAsColumns

;

DEALLOCATE cursDNamesAsColumns

;

UPDATE ##db_name SET

IsEmptyRow = 'Y'

WHERE IsEmptyRow IS NULL

;

UPDATE ##db_name SET

IsSysAdminLogin = 'N'

FROM

##db_name TT

INNER JOIN

dbo.syslogins SL

ON TT.LoginUserName = SL.[name]

WHERE

SL.sysadmin = 0

;

SELECT * FROM ##db_name

;

DROP TABLE ##db_name

;

DROP TABLE #permission

;

END TRY

BEGIN CATCH

DECLARE

@cursDBs_Status INT

,@cursSysSrvPrinName_Status INT

,@cursDatabases_Status INT

,@cursPermisTable_Status INT

,@cursDNamesAsColumns_Status INT

;

SELECT

@cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')

,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')

,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')

,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')

,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')

;

IF @cursDBs_Status > -2

BEGIN

CLOSE cursDBs ;

DEALLOCATE cursDBs ;

END

IF @cursSysSrvPrinName_Status > -2

BEGIN

CLOSE cursSysSrvPrinName ;

DEALLOCATE cursSysSrvPrinName ;

END

IF @cursDatabases_Status > -2

BEGIN

CLOSE cursDatabases ;

DEALLOCATE cursDatabases ;

END

IF @cursPermisTable_Status > -2

BEGIN

CLOSE cursPermisTable ;

DEALLOCATE cursPermisTable ;

END

IF @cursDNamesAsColumns_Status > -2

BEGIN

CLOSE cursDNamesAsColumns ;

DEALLOCATE cursDNamesAsColumns ;

END

SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;

END CATCH

EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;

Method:2SCRIPT TO LIST ALL SQL SERVER LOGINS, SERVER ROLES AND DATABASE ROLES
Here is the useful script to list all the SQL server Logins and roles. This script is useful in auditing

--#### LOGINS,USERS,ROLES (LUR) SCRIPT ####
--#### SCRIPT TESTED IN SQL SERVER 2000,2005,2008 AND 2008R2####
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
-- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES](
[DBNAME] [SYSNAME] ,
[USERNAME] [SYSNAME] ,
[DB_OWNER] [VARCHAR](3) ,
[DB_ACCESSADMIN] [VARCHAR](3) ,
[DB_SECURITYADMIN] [VARCHAR](3) ,
[DB_DDLADMIN] [VARCHAR](3) ,
[DB_DATAREADER] [VARCHAR](3) ,
[DB_DATAWRITER] [VARCHAR](3) ,
[DB_DENYDATAREADER] [VARCHAR](3) ,
[DB_DENYDATAWRITER] [VARCHAR](3) ,
[DT_CREATE] [DATETIME] NOT NULL,
[DT_UPDATE] [DATETIME] NOT NULL,
[DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3] DEFAULT (GETDATE())
) ON [PRIMARY]
GO
INSERT INTO [TEMPDB].[DBO].[DB_ROLES]
EXEC SP_MSFOREACHDB
' SELECT
''?'' AS DBNAME,
USERNAME,
MAX(CASE ROLENAME WHEN ''DB_OWNER'' THEN ''YES'' ELSE ''NO'' END) AS DB_OWNER,
MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN '' THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,
MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN'' THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,
MAX(CASE ROLENAME WHEN ''DB_DDLADMIN'' THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,
MAX(CASE ROLENAME WHEN ''DB_DATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,
MAX(CASE ROLENAME WHEN ''DB_DATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,
MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,
MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,
CREATEDATE,
UPDATEDATE,
GETDATE()
FROM (
SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE
FROM
[?].DBO.SYSMEMBERS A JOIN [?].DBO.SYSUSERS B ON A.MEMBERUID = B.UID
JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID
)S
GROUP BY USERNAME, CREATEDATE, UPDATEDATE
ORDER BY USERNAME'
-- RETRIVE LOGINS, USERS AND ROLES TOGETHER
SELECT SERVERPROPERTY('SERVERNAME') AS [SERVERNAME],
B.NAME AS [LOGINNAME],
CASE B.SYSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN,
CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN,
CASE B.SETUPADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN,
CASE B.PROCESSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN,
CASE B.DISKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN,
CASE B.DBCREATOR WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR,
CASE B.BULKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN,
B.DBNAME AS [DEFAULT_DBNAME],
A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME
--WHERE B.ISNTUSER=1
--INCLUDE TO EXCLUDE THE SQL LOGINS
SELECT * FROM #LOGINS ORDER BY [LOGINNAME]
DROP TABLE [TEMPDB].[DBO].[DB_ROLES]
DROP TABLE #LOGINS
--------------SCRIPT END-----------------------

Method:3 SQL Server List Login's Database
in the security audit of our server was to review the login's database role membership and server role membership.

There is a system stored procedure sp_helpsrvrolemember to list server role membership of login, but it is difficult to review the result of this procedure . Below script will give us the server role membership in more readable format.By copying the the result to Excel, it is more easy to review the server role membership of logins. More over system store procedure will not list the logins which have control server permission.

WITH CTE_Role (name,role,type_desc)
AS
(SELECT PRN.name,
srvrole.name AS [role] ,
Prn.Type_Desc
FROM sys.server_role_members membership
INNER JOIN (SELECT * FROM sys.server_principals WHERE type_desc='SERVER_ROLE') srvrole
ON srvrole.Principal_id= membership.Role_principal_id
RIGHT JOIN sys.server_principals PRN
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0

UNION ALL

SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p
JOIN sys.server_permissions Sp
ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G' )
SELECT
name,
Type_Desc ,
CASE WHEN [public]=1 THEN 'Y' ELSE 'N' END AS 'Public',
CASE WHEN [sysadmin] =1 THEN 'Y' ELSE 'N' END AS 'SysAdmin' ,
CASE WHEN [securityadmin] =1 THEN 'Y' ELSE 'N' END AS 'SecurityAdmin',
CASE WHEN [serveradmin] =1 THEN 'Y' ELSE 'N' END AS 'ServerAdmin',
CASE WHEN [setupadmin] =1 THEN 'Y' ELSE 'N' END AS 'SetupAdmin',
CASE WHEN [processadmin] =1 THEN 'Y' ELSE 'N' END AS 'ProcessAdmin',
CASE WHEN [diskadmin] =1 THEN 'Y' ELSE 'N' END AS 'DiskAdmin',
CASE WHEN [dbcreator] =1 THEN 'Y' ELSE 'N' END AS 'DBCreator',
CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE 'N' END AS 'BulkAdmin' ,
CASE WHEN [ControlServer] =1 THEN 'Y' ELSE 'N' END AS 'ControlServer'
FROM CTE_Role
PIVOT(
COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])

) AS pvt WHERE Type_Desc NOT IN ('SERVER_ROLE')ORDER BY name,type_desc

;
go

Method:4 SQL Server : Server Level Role Permission

The system stored procedure sp_helprolemember will help us to list the login's database role membership (system defined and user defined database role membership),but this will work only on database level. To get membership across all databases in an instance, this procedure has to run in each databases. The below script will list all users in all databases and its database role membership.

SET NOCOUNT ON

CREATE TABLE #DatabaseRoleMemberShip
(
Username VARCHAR(100),
Rolename VARCHAR(100),
Databasename VARCHAR(100)

)DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000) SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip
select u.name,r.name,''?'' from sys.database_role_members RM inner join
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd

SELECT @PivotColumnHeaders =
COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'
)
FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename ASC

SET @Cmd = 'select
databasename,username,'+@PivotColumnHeaders+'
from
(
select * from #DatabaseRoleMemberShip) as p
pivot
(
count(rolename )
for rolename in ('+@PivotColumnHeaders+') )as pvt'EXECUTE(@Cmd ) DROP TABLE #DatabaseRoleMemberShip

go

Method:5 SQL Server : Find Culprit CONTROL SERVER permission

Today morning I was working on one of the server to complete the regular documentation of security audit with details of logins with sysadmin rights and their access to various database. Later I have realized that, one login has access to all database but it is not captured by my auditing script. Here comes the culprit , the CONTROL SERVER rights.

By default CONTROL SERVER rights is equivalent to sysadmin permission except the fact that logins with CONTROL SERVER rights will honor the explicitly denied server level permission where the members of sysadmin server role bypass the explicitly denied server level permission. Also note that logins with control server permission will have implicit access to the databases like the sysadmin members and database owners.

Logins with control server rights will not have mapping entry in the sys.database_principals but it will have access to all databases. The worst part is, logins with CONROL SERVER permission are not easy to find out unless you prepare explicit query. It is not listed in the UI of SSMS or there is no system procedure like sp_helpsrvrolemember to list the logins with CONTROL SERVER right.

Let us walk through a sample script. Create two logins using the below script

CREATE login SysadminLogin WITH password ='password123~'
GO
CREATE login controlserverlogin WITH password ='password123~'
GO
EXEC sp_addsrvrolemember 'SysadminLogin','sysadmin'
GO
GRANT control server TO controlserverlogin
Now log in to the server using the controlserverlogin and you can access all the databases and perform any actions. Let us see what will happen on explicitly denying the server level permission.

DENY VIEW ANY DATABASE TO controlserverlogin
GO
DENY VIEW ANY DATABASE TO sysadminlogin
GO

Now log in to the server using both the login. You can notice that, in the session that connected with the controlserverlogin will list only Master and Tempdb databases while the session connected with sysadminlogin will list all available databases.

The other potential issue with logins having control server right is , they can add them self to the sysadmin server role or can create a new login with membership to the sysadmin server role. Fortunately it is not possible to do it in straight forward steps. Let us see how it will work .Connect to the server using the controlserverlogin and execute the below scripts

/* Fortunately this will fail */
EXEC sp_addsrvrolemember 'controlserverlogin','sysadmin';
GO
/* Unfortunately this will work even if sa account is disabled*/
EXECUTE AS LOGIN = 'sa';
GO
EXEC sp_addsrvrolemember 'controlserverlogin','sysadmin';
GO
REVERT;

Now the controlserverlogin has sysadmin role membership and you can see all available databases.

Below script list the logins with sysadmin role membership and control server permission.

/* List login with membership to Sysadmin server role and Control Server right*/
SELECT p.[name] [Login], 'sysadmin Role Member' [Access] ,p.type_desc AS loginTypeFROM sys.server_principals p
JOIN sys.server_role_members RM
ON p.principal_id = rm.member_principal_id
JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id WHERE rp.NAME = 'sysadmin' UNION ALL SELECT p.[name], 'Control Server Right' ,p.type_desc AS loginTypeFROM sys.server_principals p
JOIN sys.server_permissions Sp
ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G' GO

Below script will help to list the explicitly denied server level permission for logins which have control server permission.

/* List Explicitly denied permission for the login that has control server permission*/
SELECT CSL.*,sp.permission_name [Explicitly Denied Permission] FROM sys.server_permissions SP INNER JOIN (SELECT p.principal_id ,p.[name], p.type_desc AS loginTypeFROM sys.server_principals p
JOIN sys.server_permissions Sp
ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G' ) CSL ON CSL.principal_id = sp.grantee_principal_id
WHERE state ='D'

Method:6 SQL Server : Script to List All SQL Server Database Users and Their Access Rights into a Flat File

I have a task to produce a hard copy of all database users and each access rights of all SQL Server servers. I would like to run this once a week in batch. Is this achievable?

You can do this in many ways. If you are creating a BAT file, you can save the following code and then use your BAT to execute it. It has two option commented in the code to use a single database or all databases on the target server you enter in the BAT. I have included a sample of the batch at the bottom:

--Example of batch file to execute
--sqlcmd -S YOURTARGETSERVERHERE -E -i "C:\YOURPATHTOSQLSCRIPT\USERPERMISSIONS.SQL&qu ot; -s , -o "C:\YOURPATHHERE\UserPermisions.CSV"

Save the following files as "USERPERMISSIONS.SQL"

else run directly.

set nocount on
declare @permission table (
Database_Name sysname,
User_Role_Name sysname,
Account_Type nvarchar(60),
Action_Type nvarchar(128),
Permission nvarchar(60),
ObjectName sysname null,
Object_Type nvarchar(60)
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
--Use this for all databases
select name from sys.databases order by name
--Uncomment and use this for a single DB
--select name from 'YOURDBNAMEHERE' as dbname
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
declare @objects table (obj_id int, obj_type char(2))
insert into @objects
select id, xtype from master.sys.sysobjects
insert into @objects
select object_id, type from sys.objects

SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'',
d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'',
OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'',
case e.obj_type
when ''AF'' then ''Aggregate function (CLR)''
when ''C'' then ''CHECK constraint''
when ''D'' then ''DEFAULT (constraint or stand-alone)''
when ''F'' then ''FOREIGN KEY constraint''
when ''PK'' then ''PRIMARY KEY constraint''
when ''P'' then ''SQL stored procedure''
when ''PC'' then ''Assembly (CLR) stored procedure''
when ''FN'' then ''SQL scalar function''
when ''FS'' then ''Assembly (CLR) scalar function''
when ''FT'' then ''Assembly (CLR) table-valued function''
when ''R'' then ''Rule (old-style, stand-alone)''
when ''RF'' then ''Replication-filter-procedure''
when ''S'' then ''System base table''
when ''SN'' then ''Synonym''
when ''SQ'' then ''Service queue''
when ''TA'' then ''Assembly (CLR) DML trigger''
when ''TR'' then ''SQL DML trigger''
when ''IF'' then ''SQL inline table-valued function''
when ''TF'' then ''SQL table-valued-function''
when ''U'' then ''Table (user-defined)''
when ''UQ'' then ''UNIQUE constraint''
when ''V'' then ''View''
when ''X'' then ''Extended stored procedure''
when ''IT'' then ''Internal table''
end as ''Object Type''
FROM [' + @Next + '].sys.database_principals a
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
left join @objects e on d.major_id = e.obj_id
order by a.name, d.class_desc')
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select * from @permission

Method:7 SQL Server : Object rights

create table ##ObjRights(
[ServerName] nvarchar(128), [DatabaseName] nvarchar(128), [Owner] nvarchar(128), [Object] nvarchar(128), [Grantee] nvarchar(128), [Grantor] nvarchar(128), [ProtectType] nvarchar(20), [Action] nvarchar(50), [Column] nvarchar(4000));

declare dbs cursor for select name from master.dbo.sysdatabases;
declare @db nvarchar(128)
,@sql varchar(500);
open dbs;
fetch dbs into @db;
while @@fetch_status=0
begin
set @sql = 'use '+@db+';
insert ##ObjRights ([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column])
exec sp_helprotect;
update ##ObjRights set DatabaseName='''+@db+'''
where DatabaseName is null;'
exec(@sql);
fetch dbs into @db;
end
close dbs;
deallocate dbs;
update ##ObjRights set ServerName= case when convert(nvarchar, serverproperty('InstanceName')) is null
then convert(nvarchar, serverproperty('MachineName'))
else convert(nvarchar, serverproperty('MachineName'))+N'/'+convert(nvarchar, serverproperty('InstanceName')) end;
select * from ##ObjRights;
drop table ##ObjRights;
go

SQLSkills blogspot

Search This Blog

GUNNU

Execute Select Statement for multiple Database in MSSQL

Transfer logins and passwords between instances of SQL Server: sp_help_revlogin script

Steps on the destination server (Server B)

How to Check Monthly Growth of Database in SQL Server (Database Growth :last 6 month database grow)

--SECTION 1 BEGIN

Windows: How do I log out of a remote desktop that only gives a disconnect option?
(Log off a disconnected user remotely)

Query that returns list of all Stored Procedures in an MS SQL database

Query that returns list of all Stored Procedures Script in an MS SQL database

SQL USERS Completed Reference - Part2

SQL USERS Completed Reference - Part1

SQL USERS Completed Reference -Part1

SQL USERS Completed Reference - Part1

SQL Server query to find list of all users in a databaseSQL Server query to find all permissions/access for all users in a database

SQL USERS Completed Reference -Part2

Method:4 SQL Server : Server Level Role Permission

Method:5 SQL Server : Find Culprit CONTROL SERVER permission

Method:6 SQL Server : Script to List All SQL Server Database Users and Their Access Rights into a Flat File

Method:7 SQL Server : Object rights

SQL USERS Completed Reference -Part2

Translate

My Blog List

Search This Blog

GUNNU

Execute Select Statement for multiple Database in MSSQL

Transfer logins and passwords between instances of SQL Server: sp_help_revlogin script

Steps on the destination server (Server B)

How to Check Monthly Growth of Database in SQL Server (Database Growth :last 6 month database grow)

--SECTION 1 BEGIN

Windows: How do I log out of a remote desktop that only gives a disconnect option? (Log off a disconnected user remotely)

Query that returns list of all Stored Procedures in an MS SQL database

Query that returns list of all Stored Procedures Script in an MS SQL database

SQL USERS Completed Reference - Part2

SQL USERS Completed Reference - Part1

SQL USERS Completed Reference -Part1

SQL USERS Completed Reference - Part1SQL Server query to find list of all users in a databaseSQL Server query to find all permissions/access for all users in a database

SQL USERS Completed Reference -Part2

Method:4 SQL Server : Server Level Role Permission

Method:5 SQL Server : Find Culprit CONTROL SERVER permission

Method:6 SQL Server : Script to List All SQL Server Database Users and Their Access Rights into a Flat File

Method:7 SQL Server : Object rights

SQL USERS Completed Reference -Part2

Translate

My Blog List

Windows: How do I log out of a remote desktop that only gives a disconnect option?
(Log off a disconnected user remotely)

SQL USERS Completed Reference - Part1

SQL Server query to find list of all users in a databaseSQL Server query to find all permissions/access for all users in a database