March 20, 2014 at 3:17 am
Hi All,
I am a junior dba not a developer. So I'm just trying to get use to write code in T-SQL.
Anyways, I have a table which is dba.dbhakyedek.
Columns are
dbname, username, class_desc, object_name, permission_name, state_desc
I have statement
select case
when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username
WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC'
end
from dba.dbhakyedek
where username='MC'
This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it. Any suggestions
Thanks in advance
March 20, 2014 at 3:19 am
A proc can't be called as part of a select. What are you trying to do here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2014 at 3:24 am
What I am trying to do is;
If my class_desc returns OBJECT_OR_COLUMN, I want to get the grant statement which works. However if class_desc returns DATABASE_ROLE then i want to execute sp_addrolemember. It's like
EXEC sp_addrolemember N'object_name(which comes from dba.dbhakyedek)', N'MC'
March 20, 2014 at 6:59 am
Use dynamic SQL to set a character string with the CASE
March 20, 2014 at 7:20 am
If you change that to either get the GRANT or get the EXEC, then it's easy. Something like this (didn't test, so quotes may be off)
select case
when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username
WHEN class_desc='DATABASE_ROLE' THEN 'EXEC sp_addrolemember N''' + object_name + ''', N''MC'''
end
from dba.dbhakyedek
where username='MC'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2014 at 7:24 am
I want to execute it, I dont want to get the statement itself. Thanks for your response
March 20, 2014 at 7:44 am
You can't execute a procedure as part of a select.
You're getting the GRANT commands listed out, why do you want one set of commands printed (I assume for later execution) and the other executed immediately?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply