asked
Passing database name as Parameter
Hi, I am trying to pass database as a paramerter how can i achieve this . I need to pass database name as a parameter to the proc and the reason is I need to script out the all the user's permission in database . I am working in the user permission script but I also need to pass database name as a input parameter in store procedure so that all the user's permission on the user supplied database can be script out prior to production refresh . I am trying to achieve this what will be the alternative approch for achieving this if this is not possible create proc Test_Proc(@idb_name varchar(100) ) begin declare @db_name varchar(100) set @db_name = 'use @idb_name' exec (@db_name) select col2,col2 from sys.database_principals some coditions union select col2,col2 from sys.database_principals some condition end Thanks in advace
I would rather workaround this problem by usind three part name identifier i.e. DBNAME.SCHEMANAME.TABLENAME and then use dynamic sql. But since dynamic sql is vulnerable to sql injection, I would also make sure that the paramter is verified as a proper DBNAME etc. (Not to mention DB name parameter should be either sysname OR NVARCHAR(128)) So solution should be something like create proc Test_Proc(@idb_name sysname )--NOTE I PREFER USING SYSNAME DATATYPE begin declare @DSQL nvarchar(max) IF EXISTS(SELECT * FROM sys.databases where name = @idb_name) begin set @DSQL = N' select col2,col2 from [' + @idb_name + '].sys.database_principals some coditions union select col2,col2 from [' + @idb_name + '].sys.database_principals some condition Exec(@DSQL) --I leave using sp_executesql as an exercise for you end ELSE part to raise error etc. end Hope you got the idea.
As far as i am aware you cannot pass Database or tables names this way in SQL. Have a look at dynamic sql, to do this.
**String Concatenation** First, if you put a variable name inside of quotes like set @db_name = 'use @idb_name' Then it uses the literal string that makes up the variable name, not the value of the variable. So you generally want something like (quotename might also be appropriate depending on how it gets used): set @db_name = 'use ' + @idb_name **Execution Context** Also, when you execute a `exec 'Use [targetedb]'` command, it only changes the database it is using within that execution context, which in this case is just the content of that one exec statement. So, you could do something like: set @sql= 'use ' + @idb_name + ' select col2,col2 from sys.database_principals some coditions union select col2,col2 from sys.database_principals some condition ' exec (@sql) If you wanted. But while this works, I actually think that Usman's suggestion is more elegant and would suggest doing it his way. **Final Comments** As Usman mentioned, dynamic sql opens the door to sql injection. There are some things you can do to [mitigate this][1]. [1]:
https://www.simple-talk.com/sql/learn-sql-server/sql-injection-defense-in-depth/
ALTER PROC P_Login_Script (@IDbName sysname) AS DECLARE @Text nvarchar(max) IF EXISTS(SELECT * FROM sys.databases where name = @IDbName) BEGIN SET @Text= N'SELECT * FROM ['+ @IDbName + '].sys.database_principals EXEC (@Text)' END ELSE PRINT 'NO DATABAE EXISTS' EXEC P_Login_Script 'AdventureWorks2008' ---------- **how to get rid of error** Thanks for you input Timothy,ruancra and ussam. But When I tied to run like above the sp executes but doesn;t gives any output . and when i tired to create as Usman sample it is giving me an error. Can you help little bit. thanks
Thanks a USman there was some eror but it worked fantastically great help . ALTER PROC P_Login_Script (@IDbName sysname) AS DECLARE @Text nvarchar(max) IF EXISTS(SELECT * FROM sys.databases where name = @IDbName) BEGIN SET @Text= N'SELECT * FROM ['+ @IDbName + '].sys.database_principals' EXEC (@Text) END ELSE PRINT 'NO DATABAE EXISTS'
Actually I want to pass the database name as Parameter of function which returns the numeric value. how to do that??? Example Create Function (@database Name) Select Top1 @mrate= it returns the numeric value. and select query in it with this database. please help me.
15 People are following this question.
