Wednesday, April 25, 2012 2:12 PM
Wednesday, April 25, 2012 6:46 PM
Below can serve as a starting point. Obvously, you will want to add a WHERE clase so you don't try to drop roles, dbo and such. And when you are happy with it, remove the commend before EXEC. And you can use a similar technique for logins (sys.server_principals). If you want to check users to logins though a query, you can check the query under the logins section in http://www.karaszi.com/SQLServer/info_moving_database.asp. As for the second part, I'm not sure I follow... Are we talking about Windows logins, using the local SAM database? In that case, I think they get new SIDs when you rename the machine so you do have to re-create them...
DECLARE c CURSOR FOR SELECT name FROM sys.database_principals DECLARE @usr sysname, @sql nvarchar(1000) OPEN c WHILE 1 = 1 BEGIN FETCH NEXT FROM c INTO @usr IF @@FETCH_STATUS <> 0 BREAK SET @sql = 'DROP USER ' + QUOTENAME(@usr) PRINT @sql --EXEC(@sql) END
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Friday, May 04, 2012 3:24 AM
Wednesday, April 25, 2012 9:52 PM
I am not sure that I follow. If you clone the entire VM why would you have to drop and recreate logins? The logins are in included in the clone or what am I missing?
It's a different story if you restore a database on a different server and you use SQL logins. In this case you typically need to remap database users to logins.
I would do this as
SELECT 'ALTER USER ' + quotename(name) + ' WITH LOGIN = ' + quotename(name)
WHERE type = 'S'
AND name <> 'dbo'
and then execute the result.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org