How to rename users, add a role etc, using script in t-sql

I have to

• Rename users (from yyyyyy\xxxx to xxxx)
• Add a role to the users
• See the priviliges of stored procedures granted to a specified role (I found a table with the information regarding tables, but not stored procedure)

All in t-sql. I know how to do it mannualy, but with 400+ users开发者_开发百科, I hope to script me out of the problems.

Can anyone help?

What you need to do is loop over the users to modify and execute the commands to make the changes you need. You can do this by querying the syslogins table and creating a cursor with the results.

I have added the statement to rename the user, but adding the role is as simple as adding in a second statement and exec with sp_addrolemember

DECLARE @Login as varchar(50);

DECLARE LoginsCrsr CURSOR FOR
SELECT name
FROM syslogins
WHERE name like '%John%'; --Whatever critera you need

OPEN LoginsCrsr;

FETCH NEXT FROM LoginsCrsr
INTO @Login;

WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @TSQL as varchar(255)
   DECLARE @NewLogin as varchar(50)

   SELECT @NewLogin = @Login   -- Do your own thing here

   SELECT @TSQL = 'ALTER LOGIN [' + @Login + '] WITH NAME=[' + @NewLogin + ']' 
   PRINT @TSQL
   EXEC (@TSQL)

   --Whatever else you need to do

   FETCH NEXT FROM LoginsCrsr
   INTO  @Login
END
GO

CLOSE LoginsCrsr;
DEALLOCATE LoginsCrsr;
GO