Hi all,

Thanks for all the replies,

michael.higgins

Thanks for another method! reall helpful to see alternate ways..

GilaMonster

Interesting, so when using dynamic sql you never put in GO? How is it ran then if you cant use GO to separate statements? I thought some things needed GO like reconfigure (going out of scope i know just interested to learn 🙂 )

ScottPletcher

Excellent idea.. makes it a lot cleaner! thank you

Vegard Hagen

Yes i agree, im just testing on my own machine at the moment, im trying to get a better grip of dynamic sql and when i saw some of my dbs are in full i thought it was a good chance to to get some practice in!

Trying to take it to the next level. I wanted to restore a database to a dev box and remove all user accounts as it will only need 1 dev account.

Again im getting an error.

DECLARE @sql nvarchar(max)

SET @sql = ''

SELECT @sql = @sql+

'DROP USER '''+name+'''

'

FROM sys.database_principals

WHERE principal_id > 5 AND TYPE != 'R'

--PRINT @sql

EXECUTE (@sql)

Error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'UserA'.

Thanks all for the help!