Creating a Virtual Server Test Environment

Question

With the virtual server technology, it is so easy anymore to clone an entire server to create a test environment.  When I've done this in the past, after dropping and adding the new SQL Server name and editing the DSN to access our app (Dynamics GP), we have either manually deleted the SQL users and logins one at a time and then transferred them from the production server with a script, or just created new users with different IDs (the GP app manages the SQL logins and users and all the permissions).

However, we've come across an installation with a lot of users.

Is there a automated method that would allow us to delete ALL sql users and logins (scary, I know) - or better yet, make the existing users and logins operational with the new default server name?

Please note - I am not requesting assistance on how to delete logins or users one at a time and I am not requesting help in transferring logins and users from one SQL Server instance to another.

Cindy

---

Cindy

Answer 1

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

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

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)
  FROM   sys.database_principals
  WHERE  type = 'S'
    AND  name <> 'dbo'

and then execute the result.

---

Erland Sommarskog, SQL Server MVP, [e-mail]