quoted identifiers in stored procedure

Question

Post reply

quoted identifiers in stored procedure

george sibbald

SSC Guru

Points: 104210
More actions
April 17, 2007 at 10:45 am

#153527

Hi, can any of you coding gurus help me?
I want to put the following code in a stored procedure:
select 'exec sp_dropuser ' +"'" +name +"'" from sysusers where sid > 0x01 and (isntuser = 1 or issqluser = 1 or isntgroup = 1)
select 'exec sp_grantdbaccess ' +"'" +l.loginname +"','" +o.name +"'" from sysusers o
left join master.dbo.syslogins l on l.sid = o.sid where o.sid > 0x01 and (o.isntuser = 1 or o.issqluser = 1 or o.isntgroup = 1)
I have set quoted_identifier off
However the stored procedure will not compile with error invalid column name ''' i.e. it does not like my quoted identifiers
The code works fine as straight SQL. Am I doing something wrong or is this not possible in a proc?

---------------------------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

Can you show us what you want the final SQL to look like as returned from the SELECT statement?

george sibbald SSC Guru Points: 104210 More actions · Answer 2

David,

I am trying to produce the sp_dropuser and sp_grantdbaccess commands for all users in the database, with the quotes required for windows authenticated users, i.e.

exec sp_dropuser 'username'

exec sp_grantdbaccess 'domain\name','name'

Its getting the quotes that troublesome. As I said the code above works from QA but will not compile as part of a stored proc

george

---------------------------------------------------------------------

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Avoid the use of double quotes and use single quotes

select 'exec sp_dropuser ' + '''' + [name] + '''' from sysusers where sid > 0x01 and (isntuser = 1 or issqluser = 1 or isntgroup = 1)

or

select 'exec sp_dropuser ' + CHAR(39) + [name] + CHAR(39) from sysusers where sid > 0x01 and (isntuser = 1 or issqluser = 1 or isntgroup = 1)

or

select 'exec sp_dropuser ' + QUOTENAME([name],'''') from sysusers where sid > 0x01 and (isntuser = 1 or issqluser = 1 or isntgroup = 1)

Far away is close at hand in the images of elsewhere.
Anon.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

david, that's great, thanks. I knew it would be embaressingly easy

---------------------------------------------------------------------