December 30, 2013 at 9:25 am
Hi,
I am looking for script to extract Database and Object level permissions with Create_Date.
Please advise.
Regards,
SQLisAwe5oMe.
December 30, 2013 at 9:40 am
you can get the date an object is created from sys.objects create_date and modify_date.(tables/procs/views etc)
you cannot get when a permission was granted. that date is not stored anywhere.
if you could explain what, maybe with an example, you are looking for, we could help you better.
there are a ton of scripts in the scripts section here on SSC for scripting out permissions, i would end up finding one there myself, just to paste it here, if i knew what you were lookign for.
Lowell
December 30, 2013 at 9:55 am
Lowell (12/30/2013)
you can get the date an object is created from sys.objects create_date and modify_date.(tables/procs/views etc)you cannot get when a permission was granted. that date is not stored anywhere.
if you could explain what, maybe with an example, you are looking for, we could help you better.
there are a ton of scripts in the scripts section here on SSC for scripting out permissions, i would end up finding one there myself, just to paste it here, if i knew what you were lookign for.
Hi Lowell, thanks for your reply.
Basically, I need to extract all the users, db roles, objects, permissions(execute, etc) from all the databases in an instance with create date.
So, the out put of the query should have the followings:
ServerName, Login, User, Role, LoginType, DatabaseName, ObjectName, Permission, Create_date, Last_Modify, etc.
I have a script which does this for DB level, but I need to include object level as well. I am pasting the script for your review. Please let me know if you can modify to include the additional requirements. Thanks.
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
IF OBJECT_ID('TmpUsers') IS NOT NULL
DROP TABLE TmpUsers
/*
CREATE TABLE TmpUsers (
id INT IDENTITY(1,1),
DbName VARCHAR(256),
loginname VARCHAR(256),
UserName VARCHAR(256),
RoleName VARCHAR(256)
)
*/
CREATE Table TmpUsers (
[Server] VARCHAR(256),
[Login] VARCHAR(256),
[User] VARCHAR(256),
[Role] VARCHAR(256),
SSID VARCHAR(256),
[Login Type] VARCHAR(256),
[Database Name] VARCHAR(256),
[Create Date] datetime,
[Last Modify] DATETIME
)
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'sysadmin' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
--INTO TmpUsers
FROM MASTER..syslogins l1
WHERE l1.sysadmin = 1
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'securityadmin' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
FROM MASTER..syslogins l1
WHERE l1.securityadmin = 1
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'serveradmin' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
FROM MASTER..syslogins l1
WHERE l1.serveradmin = 1
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'setupadmin' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
FROM MASTER..syslogins l1
WHERE setupadmin = 1
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'processadmin' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
FROM MASTER..syslogins l1
WHERE processadmin = 1
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'diskadmin' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
FROM MASTER..syslogins l1
WHERE diskadmin = 1
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'dbcreator' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
FROM MASTER..syslogins l1
WHERE dbcreator = 1
INSERT TmpUsers ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT
@@SERVERNAME [Server],
NAME [Login],
NULL [User],
'bulkadmin' [Role],
'' as [SSID],
CASE
WHEN isntuser = 1 THEN 'Windows User'
WHEN isntgroup = 1 THEN 'Windows Group'
ELSE 'SQL User'
END AS [Login Type],
'Server Level' as [Database Name],
createdate AS [Create Date],
updatedate AS [Last Modify]
FROM MASTER..syslogins l1
WHERE bulkadmin = 1
DECLARE @databaselist TABLE
(id INT IDENTITY(1,1) , dbname sysname)
INSERT INTO @databaselist (dbname)
SELECT NAME
FROM MASTER..sysdatabases
DECLARE @dbname sysname
Declare @N int
Declare @MaxN int
Set @N=(Select min(id) from @databaselist)
Set @MaxN = (Select max(id) from @databaselist)
Declare @var varchar(256)
Declare @int int
DECLARE @strSQL VARCHAR(8000)
While @N<=@MaxN
Begin
If Exists(Select id from @databaselist where id=@N)
Begin
Set @dbname=(Select dbname from @databaselist where id=@N)
If (SELECT DATABASEPROPERTYEX(@dbname, 'STATUS'))='ONLINE'
BEGIN
SET @strSQL = '
IF OBJECT_ID(''tempdb..#RoleList'') IS NOT NULL
DROP TABLE #RoleList
CREATE TABLE #RoleList (
RoleName sysname,
UserName sysname,
LoginSid VARBINARY(85)
)
INSERT INTO #RoleList
EXEC ' + '['+@dbname+']' + '.dbo.sp_helprolemember
INSERT INTO [TmpUsers] ([Server], [Login], [User], [Role], SSID, [Login Type], [Database Name],[Create Date],[Last Modify])
SELECT @@SERVERNAME,
loginname,
UserName,
RoleName,
'''',
CASE
WHEN sysusers.isntuser = 1 THEN ''Windows User''
WHEN sysusers.isntgroup = 1 THEN ''Windows Group''
ELSE ''SQL User''
END AS [Login Type],
''' + @dbname + ''' DBName,
sysusers.createdate AS [Create Date],
sysusers.updatedate AS [Last Modify]
FROM #RoleList
JOIN ' + '['+@dbname+']' + '..sysusers sysusers ON [#RoleList].LoginSid = sysusers.sid AND hasdbaccess = 1
LEFT OUTER JOIN [master]..syslogins syslogins ON [#RoleList].LoginSid = syslogins.sid
'
exec (@strSQL)
End
END
Set @N=@N+1
END
SELECT * FROM TmpUsers
Regards,
SQLisAwe5oMe.
December 30, 2013 at 12:44 pm
See if this script helps. I use this to script out users/roles/permissions before a db refresh.
DECLARE
@sql VARCHAR(2048)
,@sort INT
DECLARE tmp CURSOR FOR
/*********************************************/
/********* DB CONTEXT STATEMENT *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB USER CREATION *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_role_members AS rm
WHEREUSER_NAME(rm.member_principal_id) IN (
--get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
)
--ORDER BY rm.role_principal_id ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
9 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHEREusr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
12 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
--WHEREusr.name = @OldUser
WHERE[perm].[major_id] = 0
AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
UNION
SELECT '' AS [-- SQL STATEMENTS --],
13 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
15 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.major_id = s.schema_id
inner join sys.database_principals dbprin
on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema
ORDER BY [-- RESULT ORDER HOLDER --]
OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
FETCH NEXT FROM tmp INTO @sql, @sort
END
CLOSE tmp
DEALLOCATE tmp
December 30, 2013 at 6:30 pm
Lowell (12/30/2013)
you can get the date an object is created from sys.objects create_date and modify_date.(tables/procs/views etc)you cannot get when a permission was granted. that date is not stored anywhere.
if you could explain what, maybe with an example, you are looking for, we could help you better.
there are a ton of scripts in the scripts section here on SSC for scripting out permissions, i would end up finding one there myself, just to paste it here, if i knew what you were lookign for.
Lowell, any suggestions after seeing the script?...I would appreciate any inputs..
Regards,
SQLisAwe5oMe.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply