Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Granting access to a DB and DB Role for a group of users

Avatar of AkermanIT
AkermanIT asked on
Microsoft SQL Server
7 Comments1 Solution1056 ViewsLast Modified:
I am moving a database from a SQL 2000 server to a new SQL 2005 server.  I ran a script that created all of the logins in security/logins on the 2005 server and retained the passwords.  What I don't have however is access to the database on the new server.  The database on the new server has a Database role called 'Elite'.  Essentially, I need to find a scripted way of granting the over 2000 objects in security/logins access to a database called 'son_db'...as well as give them access to the role called 'Elite' in that database.  

I came up with the following, but so far it's not working....I may be on the wrong track here so I'd appreciate any help that could be provided!


USE son_db
Select [Name] INTO UserList From Master..SysLogins
GO

DECLARE aCURSOR Cursor
FOR
Select Name From UserList

Open aCursor

DECLARE @Name NVarChar(20)
While (@@FETCH_STATUS <> -1)
BEGIN

Fetch NEXT FROM aCursor INTO @Name

EXEC sp_grantdbaccess 'son_db', @Name
EXEC sp_addRoleMember 'Elite', @Name

END
CLOSE aCURSOR
DEALLOCATE aCURSOR
GO
Drop Table UserList
GO
ASKER CERTIFIED SOLUTION
Avatar of nmcdermaid
nmcdermaid

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answers