"USE DATABASE" in stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » "USE DATABASE" in stored procedure

13 posts, Page 1 of 2

1 2 »»

"USE DATABASE" in stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

Ranzz

Posted Tuesday, January 08, 2013 3:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:42 PM
Points: 138, Visits: 510

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

Post #1404469

Alan.B

Posted Tuesday, January 08, 2013 3:51 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223, Visits: 1,137

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

-- AJB
xmlsqlninja.com

Post #1404480

Ranzz

Posted Wednesday, January 09, 2013 10:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:42 PM
Points: 138, Visits: 510

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.

Post #1404887

GSquared

Posted Wednesday, January 09, 2013 11:48 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #1404923

Ranzz

Posted Wednesday, January 09, 2013 12:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:42 PM
Points: 138, Visits: 510

Thanks for your replay GSquared Smile

Trying to insert the statement "Use Database" into a temp table?

Post #1404945

Alan.B

Posted Wednesday, January 09, 2013 12:44 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223, Visits: 1,137

Danzz (1/9/2013)

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

-- AJB
xmlsqlninja.com

Post #1404953

Ranzz

Posted Wednesday, January 09, 2013 1:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:42 PM
Points: 138, Visits: 510

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

Post #1404979

ScottPletcher

Posted Wednesday, January 09, 2013 3:33 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1405048

Alan.B

Posted Wednesday, January 09, 2013 3:51 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223, Visits: 1,137

Danzz (1/9/2013)

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

-- AJB
xmlsqlninja.com

Post #1405061

ScottPletcher

Posted Wednesday, January 09, 2013 4:01 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1405063

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions