February 28, 2013 at 1:53 am
Comments posted to this topic are about the item Security Change Snapshot
_____________________________________________________________________
- Nate
February 28, 2013 at 11:30 am
I found bug with complexe database name, example : SharePoint_AdminContent_3a1c2e47-b402-4304-b38d-c036ebea9f6b
February 28, 2013 at 11:38 am
using QUOTENAME() around the database name should correct your issue.
February 28, 2013 at 11:51 am
Doh! Brackets.
I've also added an orphaned users section at the end.
SET NOCOUNT ON;
DECLARE @login NVARCHAR(128)
, @ModDate DATETIME
, @Database NVARCHAR(128)
, @sql NVARCHAR(MAX);
--SET @login = 'applogin';
--SET @ModDate = '12/12/2012';
SET @Database = 'SharePoint_AdminContent_3a1c2e47-b402-4304-b38d-c036ebea9f6b';
----- SERVER LOGINS -----
IF @Database IS NULL
SELECT[ServerLogin] = sp.name
, [LoginType] = sp.type_desc
, [ServerRole] = STUFF((
SELECT',' + sp2.name
FROMmaster.sys.server_role_members rm
INNER JOIN master.sys.server_principals sp2 ON rm.role_principal_id = sp2.principal_id
WHEREsp.principal_id = rm.member_principal_id
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
, [DefaultDB] = sp.default_database_name
, [LastModified] = sp.modify_date
FROMmaster.sys.server_principals sp
WHEREsp.is_disabled = 0
ANDsp.name = ISNULL(@Login, sp.name)
ANDsp.modify_date >= ISNULL(@ModDate,sp.modify_date)
ORDER BY sp.name;
----- DATABASE PERMISSIONS -----
CREATE TABLE #DB (
[Database] NVARCHAR(128)
, [PermissionState] NVARCHAR(60)
, [Permission] NVARCHAR(128)
, [Object] NVARCHAR(128)
, [Login] NVARCHAR(128)
, LoginType NVARCHAR(60)
, [LastModified] DATETIME
);
SET @sql = 'USE [?];
INSERT INTO #DB (
[Database]
, [PermissionState]
, [Permission]
, [Object]
, [Login]
, LoginType
, [LastModified]
)
SELECT[Database] = DB_NAME()
, [PermissionState] = p.state_desc
, [Permission] = p.permission_name
, [Object] = CASE WHEN p.class = 0 THEN ''DB: '' + DB_NAME(p.major_id)
WHEN p.class = 3 THEN ''Schema: '' + s.name
ELSE ''Object: '' + OBJECT_NAME(p.major_id)
END
, [Login] = dp.name
, LoginType = dp.type_desc
, [LastModified] = dp.modify_date
FROMsys.database_principals dp
INNER JOIN sys.database_permissions p ON dp.principal_id = p.grantee_principal_id
LEFT OUTER JOIN sys.objects so ON p.major_id = so.object_id AND p.class = 1
LEFT OUTER JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHEREdp.name = ISNULL(' + CASE WHEN @login IS NOT NULL THEN '''' + @login + '''' ELSE 'NULL' END + ', dp.name)
ANDdp.modify_date >= ISNULL(' + CASE WHEN @ModDate IS NOT NULL THEN '''' + CONVERT(VARCHAR,@ModDate) + '''' ELSE 'NULL' END + ',dp.modify_date);';
EXECUTE sp_MSforeachdb @sql;
SELECT[Database]
, [PermissionState]
, [Permission]
, [Object]
, [Login]
, LoginType
, [LastModified]
FROM#DB
WHERE[Database] = ISNULL(@Database, [Database])
ORDER BY [Database]
, [Login]
, [Object];
----- ROLE MEMBERS -----
CREATE TABLE #RM (
[Database] NVARCHAR(128)
, [Role] NVARCHAR(128)
, [Login] NVARCHAR(512)
, [LastModified] DATETIME
);
SET @sql = 'USE [?];
SET QUOTED_IDENTIFIER ON;
INSERT INTO #RM (
[Database]
, [Role]
, [Login]
, [LastModified]
)
SELECT[Database] = DB_NAME()
, [Role]
, [Login]
, [LastModified]
FROM(
SELECT[Role] = sp.name
, [Login] = STUFF((
SELECT'','' + sp2.name
FROMsys.database_role_members rm
INNER JOIN sys.database_principals sp2 ON rm.member_principal_id = sp2.principal_id
WHERErm.role_principal_id = sp.principal_id
ANDsp2.name = ISNULL(' + CASE WHEN @login IS NOT NULL THEN '''' + @login + '''' ELSE 'NULL' END + ', sp2.name)
ANDsp2.modify_date >= ISNULL(' + CASE WHEN @ModDate IS NOT NULL THEN '''' + CONVERT(VARCHAR,@ModDate) + '''' ELSE 'NULL' END + ', sp2.modify_date)
FOR XML PATH(''''),TYPE).value(''.'',''VARCHAR(MAX)''),1,1,'''')
, [LastModified] = sp.modify_date
FROMsys.database_principals sp
WHEREsp.type IN (''R'',''A'') --DATABASE_ROLE,APPLICATION_ROLE
) x
WHEREx.[Login] IS NOT NULL;';
EXECUTE sp_MSforeachdb @sql;
SELECT[Database]
, [Role]
, [Login]
, [LastModified]
FROM#RM
WHERE[Database] = ISNULL(@Database, [Database])
ORDER BY [Database]
, [Role]
, [Login];
----- ORPHAN USERS -----
CREATE TABLE #OU (
[Database] NVARCHAR(128)
, [Login] NVARCHAR(128)
, [LastModified] DATETIME
);
SET @sql = 'USE [?];
SET QUOTED_IDENTIFIER ON;
INSERT INTO #OU (
[Database]
, [Login]
, [LastModified]
)
SELECT[Database] = DB_NAME()
, d.name
, d.modify_date
FROMsys.database_principals d
LEFT OUTER JOIN sys.server_principals s
ON d.sid = s.sid
WHEREs.sid IS NULL
ANDd.type IN (''U'', ''S'') -- WINDOWS_USER, SQL_USER
ANDd.name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys'');'
EXECUTE sp_MSforeachdb @sql;
SELECT[Database]
, [Login]
, [LastModified]
FROM#OU
WHERE[Database] = ISNULL(@Database, [Database])
ORDER BY [Database]
, [Login];
DROP TABLE #DB;
DROP TABLE #RM;
DROP TABLE #OU;
_____________________________________________________________________
- Nate
March 1, 2013 at 8:22 am
two isues:
one: already metioned. spaces and dashes in the database names:
USE [' + '?'+ '];
OR just USE [?]; as stated.
two: temprary tables drop only if the script runs successfully. When the script fails you get stuck with the one of the temp tables already being there. It is a better idea to drop temp tables just before creating them:
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#DB]') AND type in (N'U'))
DROP TABLE [tempdb].[dbo].[#DB];
Alex Donskoy
SQL DBA Greenberg & Trauriq PA
MIAMI, FL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply