January 8, 2013 at 3:32 pm
Need a favor..!
The below Proc works fine when I execute, The Proc will generate script of permissions for the database passed as parameter.
My concern is it wont generate with USE database in it.
I tried various ways to generate with USE database and I failed to do it.
Can someone please help..!
USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[ScriptoutPermissions] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ScriptoutPermissions]
@db_name [varchar](100) = Null,
@location [varchar](100) = Null
WITH EXECUTE AS CALLER
AS
begin
set nocount on
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 @login_name sysname ,@sql varchar(1000)
DECLARE @Date VARCHAR(20)
set @login_name = null
if @db_name is null
begin
raiserror('Procedure requires db_name',16,1,50001)
goto endofproc
end
create table ##permission_info (id int primary key identity, information varchar(8000))
DECLARE @defaultdb sysname
create table #login_info(sid varbinary(85),name sysname,type char(1),is_disabled bit,default_database_name sysname,hasaccess int,denylogin int)
-- DECLARE login_curs CURSOR FOR
set @sql= 'SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN master.sys.syslogins l ON ( l.name = p.name )
inner join ['+@db_name+'].sys.sysusers u ON (l.sid =U.SID)
WHERE p.type IN ( '''+'S'+''','''+'G'+''','''+'U'+''' ) AND p.name not in ( '''+'sa'+''','''+'SamPull'+''')'
insert into #login_info exec (@sql)
if exists(select 1 from #login_info)
begin
DECLARE login_curs CURSOR FOR select * from #login_info
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
END
SET @tmpstr = '/* Creating Login information '
insert into ##permission_info(information) values(@tmpstr)
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
insert into ##permission_info(information) values(@tmpstr)
insert into ##permission_info(information) values('')
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
insert into ##permission_info(information) values('')
SET @tmpstr = '-- Login: ' + @name
insert into ##permission_info(information) values(@tmpstr)
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
set @tmpstr = 'if not exists(select * from master.sys.server_principals where name = '''+@name+''')
begin
CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
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 = 'if not exists(select * from master.sys.server_principals where name =''' + @name +''')
begin
'
SET @tmpstr = @tmpstr+'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
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
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 @tmpstr=@tmpstr+'
end'
insert into ##permission_info(information) values(@tmpstr)
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
end
/*creating roles*/
set @sql= 'select ''create role [''+name+''];'' from ['+@db_name+'].sys.database_principals where type=''R'' and principal_id not between 16384 and 16393 and principal_id <>0'
insert into ##permission_info(information) values('/* Creating Database Roles*/')
insert into ##permission_info(information) exec (@sql)
/* creating database user*/
set @sql='select ''IF NOT EXISTS (SELECT 1 FROM ['+@db_name+'].SYS.database_principals SU INNER JOIN MASTER.SYS.server_principals SL ON SU.SID=SL.SID WHERE SU.NAME= ''''''+NAME+'''''')
BEGIN
IF EXISTS (SELECT 1 FROM ['+@DB_NAME+'].SYS.database_principals WHERE NAME =''''''+NAME+'''''')
DROP USER ''+ NAME+''
CREATE USER '' +name+ '' FOR LOGIN ''+NAME+'' WITH DEFAULT_SCHEMA = ''+DEFAULT_SCHEMA_NAME +''
END''from ['+@db_name+'].sys.database_principals WHERE PRINCIPAL_ID>4 AND TYPE_DESC =''SQL_USER'''
insert into ##permission_info(information) values('/* GRANTING PERMISSIONS TO DATABASE*/')
insert into ##permission_info(information) exec (@sql)
--print @sql
/* GRANTING DBROLE PERMISSIONS*/
set @sql ='select ''exec sp_addrolemember ''''''+dpr.name+'''''',''''''+dpm.name+'''''';''
from ['+@db_name+'].sys.database_principals dpr
inner join ['+@db_name+'].sys.database_role_members drm on dpr.principal_id=drm.role_principal_id
inner join ['+@db_name+'].sys.database_principals dpm on drm.member_principal_id=dpm.principal_id
where dpm.name<>''dbo'''
insert into ##permission_info(information) exec (@sql)
insert into ##permission_info(information) values('/* GRANTING DBROLE PERMISSIONS*/')
SET @sql='select ''Grant ''+DPER.PERMISSION_NAME COLLATE DATABASE_DEFAULT +'' ON OBJECT::''+sch.name+''.''+obj.name+'' TO ''+dpri.name
from ['+@db_name+'].sys.database_permissions dper
inner join ['+@db_name+'].sys.database_principals dpri on dper.grantee_principal_id = dpri.principal_id
inner join ['+@db_name+'].sys.objects obj on dper.major_id=obj.object_id
inner join ['+@db_name+'].sys.schemas sch on obj.schema_id=sch.schema_id
where class=1 and major_id>0 and state_desc=''GRANT'''
--print @sql
insert into ##permission_info(information) EXEC (@SQL)
--select information from ##permission_info order by id
drop table #login_info
--
if @location is not null
BEGIN
SELECT @date = CONVERT(VARCHAR(10),GETDATE(),112)
set @sql= 'exec master..xp_cmdshell ''bcp "select information from ##permission_info where information is not null order by id" queryout '+'"' + @location+'\'+@db_name+'_DBuserlist_' + @Date + '.txt" -T -c'''
exec (@sql)
END
ELSE
BEGIN
SELECT INFORMATION FROM ##PERMISSION_INFO
END
--select information from ##permission_info order by id
drop table ##permission_info
endofproc:
end --proc
GO
January 8, 2013 at 3:51 pm
Add USE statement to a proc (using DSQL):
CREATE PROC xxx AS
BEGIN
DECLARE @sql varchar(100)='USE tempdb; SELECT * FROM sys.database_files;'
EXEC(@sql);
SET @sql='USE master; SELECT * FROM sys.database_files;'
EXEC(@sql);
END
You can also accomplish this using the unsupported/undocumented sp_msforeachDB
EXEC sp_MSforeachdb 'USE [?]; --do something'
Edit: added note about sp_msforeachdb
-- Itzik Ben-Gan 2001
January 9, 2013 at 10:14 am
Thanks for your reply Alan
I tried couple of ways, It did not work out
create table ##permission_info (id int primary key identity, information varchar(8000))
insert into ##permission_info(information) values('/* USE Database*/')
insert into ##permission_info(information) exec (@SQL)
SET @sql= 'USE ['+@db_name+'].sys.database'
------------------------------------------------------------------------------
insert into ##permission_info(information) values('')
SET @tmpstr = 'USE + @db_name'
Any help is greatly appreciated!
Thanks in advance.
January 9, 2013 at 11:48 am
I don't follow what you're trying to do. Are you trying to get the proc to execute in each database, or trying to get it to insert the statement "Use Database" into a temp table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2013 at 12:29 pm
Thanks for your replay GSquared 🙂
Trying to insert the statement "Use Database" into a temp table?
January 9, 2013 at 12:44 pm
Danzz (1/9/2013)
Thanks for your reply AlanI tried couple of ways, It did not work out
create table ##permission_info (id int primary key identity, information varchar(8000))
insert into ##permission_info(information) values('/* USE Database*/')
insert into ##permission_info(information) exec (@SQL)
SET @sql= 'USE ['+@db_name+'].sys.database'
------------------------------------------------------------------------------
insert into ##permission_info(information) values('')
SET @tmpstr = 'USE + @db_name'
Any help is greatly appreciated!
Thanks in advance.
No Problem.
A few things to note:
1) My rule of thumb is: when I add this code to a query: CREATE TABLE ##{table} the very next line I add is the IF OBJECT_ID... code and DROP TABLE ##{table} code you see below before doing anything else. This so I don't forget.
2)There is no sys.database table but there is a sys.databases table this table, however is available regardless of your databases context (what database you are currently using). Therefore you do not need a USE statement.
3) I used sys.database_files in my previous example because, context matters. This I will demonstrate by using sys.database_files where db context DOES matter and, therefore, the USE statement is needed.
4) Your exec (@SQL) is happening before you define @sql...
Hopefully this code will clear things up:
IF OBJECT_ID('tempdb..##permission_info') IS NOT NULL
DROP TABLE ##permission_info;
create table ##permission_info (id int primary key identity, information varchar(8000))
DECLARE @sql varchar(100), @db_name varchar(10)='master'
SET @sql= 'USE ['+@db_name+'];'+CHAR(13);
SET @sql=@SQL+'insert into ##permission_info(information) SELECT [name] FROM sys.database_files'
EXEC(@SQL);
SELECT * FROM ##permission_info
DROP TABLE ##permission_info
GO
-- Itzik Ben-Gan 2001
January 9, 2013 at 1:28 pm
Thanks for all your help..
1)I am not looking databases_files in the Out put.
Example:
DBA_Databasename_Data
DBA_Databasename_Log
2)I am looking for "Use Database" in the our put.
Example:
USE DBA_Databasename
January 9, 2013 at 3:33 pm
If you just want to run the proc for a single db at a time, you don't need dynamic SQL at all, so I suggest avoiding it. The code's much easier to read w/o it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 9, 2013 at 3:51 pm
Danzz (1/9/2013)
Thanks for all your help..1)I am not looking databases_files in the Out put.
Example:
DBA_Databasename_Data
DBA_Databasename_Log
2)I am looking for "Use Database" in the our put.
Example:
USE DBA_Databasename
AJB:
3) I used sys.database_files in my previous example because, context matters. This I will demonstrate by using sys.database_files where db context DOES matter and, therefore, the USE statement is needed.
Play around with the code I showed you. It will show you how to get USE DATABASE into a store proc as the title of this thread suggests. That's what I was showing you.
EDIT: Added note about playing with the code
-- Itzik Ben-Gan 2001
January 9, 2013 at 4:01 pm
If you start your procedure name with "sp_", put it in the master db, and mark it as a system object, it can execute in the context of the current db or any specified db, without resorting to dynamic SQL.
USE master
GO
CREATE PROCEDURE sp_ScriptoutPermissions
@...
AS
...
GO
EXEC EXEC sp_MS_marksystemobject 'sp_ScriptoutPermissions'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 10, 2013 at 10:12 am
Danzz (1/9/2013)
Thanks for your replay GSquared 🙂Trying to insert the statement "Use Database" into a temp table?
Per your post immediately prior to my question:
create table ##permission_info (id int primary key identity, information varchar(8000))
insert into ##permission_info(information) values('/* USE Database*/')
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 10, 2013 at 10:51 am
My appologies. In my last post I misread your question. Hopefully what GSquared posted helped.
-- Itzik Ben-Gan 2001
January 10, 2013 at 12:54 pm
Thanks GSquared!!
I was able to get /* USE Database*/ it as comment section box.
The use of this SP is to ScriptoutPermissions for a database.
example:
EXEC dbo.UD_GetListDBAccess 'DBA_DBname'
Output:
/* Creating Login information
** Generated Jan 10 2013 1:47PM on USSTLPMPINFDB63 */
-- Login: Login_name
if not exists(select * from master.sys.server_principals where name = 'Login_name)
begin
CREATE LOGIN [Login_name] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
end
/* Creating Database Roles*/
/* GRANTING PERMISSIONS TO DATABASE*/
exec sp_addrolemember 'db_owner','Login_name';
exec sp_addrolemember 'db_ddladmin','Login_name';
exec sp_addrolemember 'db_datareader','Login_name';
exec sp_addrolemember 'db_datawriter','Login_name';
/* GRANTING DBROLE PERMISSIONS*/
Form my above example USE DBA_DBname is missing.
So, I am looking to modify the SP to get USE DBA_DBname in to the Out put file.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply