AkermanIT
asked on
Granting access to a DB and DB Role for a group of users
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
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
Thank you for the reply. I tried running what you said and I think I'm closer, I'm sure I'm just missing something stupid here. Look below for the results when I ran that on a test database. I don't see the users in the logins section of the server populated in the users section of the database after running this...which in turn will keep the piece about adding the role from working (at least thats my assumption) As far as your comment about Windows accounts vs Sql accounts...I couldn't agree more....unfortunately it's not my call though lol.
Thanks!
EXEC sp_grantdbaccess 'sa'
EXEC sp_grantdbaccess '##MS_SQLResourceSigningCe
EXEC sp_grantdbaccess '##MS_SQLReplicationSignin
EXEC sp_grantdbaccess '##MS_SQLAuthenticatorCert
EXEC sp_grantdbaccess '##MS_AgentSigningCertific
EXEC sp_grantdbaccess 'BUILTIN\Administrators'
EXEC sp_grantdbaccess 'NT AUTHORITY\SYSTEM'
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSSQLU
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005SQLAge
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSFTEU
EXEC sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE'
EXEC sp_grantdbaccess 'MIAMI\SQLServer'
EXEC sp_grantdbaccess 'User2'
EXEC sp_grantdbaccess 'User1'
(14 row(s) affected)
--------------------------
EXEC sp_AddRoleMember 'Test Role','sa'
EXEC sp_AddRoleMember 'Test Role','##MS_SQLResourceSig
EXEC sp_AddRoleMember 'Test Role','##MS_SQLReplication
EXEC sp_AddRoleMember 'Test Role','##MS_SQLAuthenticat
EXEC sp_AddRoleMember 'Test Role','##MS_AgentSigningCe
EXEC sp_AddRoleMember 'Test Role','BUILTIN\Administrat
EXEC sp_AddRoleMember 'Test Role','NT AUTHORITY\SYSTEM'
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer2005
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer2005
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer2005
EXEC sp_AddRoleMember 'Test Role','NT AUTHORITY\NETWORK SERVICE'
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer'
EXEC sp_AddRoleMember 'Test Role','User2'
EXEC sp_AddRoleMember 'Test Role','User1'
(14 row(s) affected)
Thanks!
EXEC sp_grantdbaccess 'sa'
EXEC sp_grantdbaccess '##MS_SQLResourceSigningCe
EXEC sp_grantdbaccess '##MS_SQLReplicationSignin
EXEC sp_grantdbaccess '##MS_SQLAuthenticatorCert
EXEC sp_grantdbaccess '##MS_AgentSigningCertific
EXEC sp_grantdbaccess 'BUILTIN\Administrators'
EXEC sp_grantdbaccess 'NT AUTHORITY\SYSTEM'
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSSQLU
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005SQLAge
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSFTEU
EXEC sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE'
EXEC sp_grantdbaccess 'MIAMI\SQLServer'
EXEC sp_grantdbaccess 'User2'
EXEC sp_grantdbaccess 'User1'
(14 row(s) affected)
--------------------------
EXEC sp_AddRoleMember 'Test Role','sa'
EXEC sp_AddRoleMember 'Test Role','##MS_SQLResourceSig
EXEC sp_AddRoleMember 'Test Role','##MS_SQLReplication
EXEC sp_AddRoleMember 'Test Role','##MS_SQLAuthenticat
EXEC sp_AddRoleMember 'Test Role','##MS_AgentSigningCe
EXEC sp_AddRoleMember 'Test Role','BUILTIN\Administrat
EXEC sp_AddRoleMember 'Test Role','NT AUTHORITY\SYSTEM'
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer2005
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer2005
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer2005
EXEC sp_AddRoleMember 'Test Role','NT AUTHORITY\NETWORK SERVICE'
EXEC sp_AddRoleMember 'Test Role','MIAMI\SQLServer'
EXEC sp_AddRoleMember 'Test Role','User2'
EXEC sp_AddRoleMember 'Test Role','User1'
(14 row(s) affected)
So when you ran these:
EXEC sp_grantdbaccess 'sa'
EXEC sp_grantdbaccess '##MS_SQLResourceSigningCe
EXEC sp_grantdbaccess '##MS_SQLReplicationSignin
EXEC sp_grantdbaccess '##MS_SQLAuthenticatorCert
EXEC sp_grantdbaccess '##MS_AgentSigningCertific
EXEC sp_grantdbaccess 'BUILTIN\Administrators'
EXEC sp_grantdbaccess 'NT AUTHORITY\SYSTEM'
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSSQLU
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005SQLAge
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSFTEU
EXEC sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE'
EXEC sp_grantdbaccess 'MIAMI\SQLServer'
EXEC sp_grantdbaccess 'User2'
EXEC sp_grantdbaccess 'User1'
were you in the database that you wanted to grant access?
Also there are various accounts that shouldn't need to be in that list like:
EXEC sp_grantdbaccess 'sa'
EXEC sp_grantdbaccess 'BUILTIN\Administrators'
EXEC sp_grantdbaccess 'NT AUTHORITY\SYSTEM'
Because they already have access or are system logins that don't need access.
EXEC sp_grantdbaccess 'sa'
EXEC sp_grantdbaccess '##MS_SQLResourceSigningCe
EXEC sp_grantdbaccess '##MS_SQLReplicationSignin
EXEC sp_grantdbaccess '##MS_SQLAuthenticatorCert
EXEC sp_grantdbaccess '##MS_AgentSigningCertific
EXEC sp_grantdbaccess 'BUILTIN\Administrators'
EXEC sp_grantdbaccess 'NT AUTHORITY\SYSTEM'
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSSQLU
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005SQLAge
EXEC sp_grantdbaccess 'MIAMI\SQLServer2005MSFTEU
EXEC sp_grantdbaccess 'NT AUTHORITY\NETWORK SERVICE'
EXEC sp_grantdbaccess 'MIAMI\SQLServer'
EXEC sp_grantdbaccess 'User2'
EXEC sp_grantdbaccess 'User1'
were you in the database that you wanted to grant access?
Also there are various accounts that shouldn't need to be in that list like:
EXEC sp_grantdbaccess 'sa'
EXEC sp_grantdbaccess 'BUILTIN\Administrators'
EXEC sp_grantdbaccess 'NT AUTHORITY\SYSTEM'
Because they already have access or are system logins that don't need access.
ASKER
Thank you for the script. That has me alot closer...I think I'm missing something very minor. When I ran the script it looked like it ran with no problem. However it doesn't add any of the users to the database. I can see where it runs all of the EXEC sp_grantdbaccess 'user' statements....and if I run one of those statements by itself it works, but it's not doing it for all of them. Here is an excerpt from the results:
EXEC sp_grantdbaccess 'AABRAHAM'
EXEC sp_grantdbaccess 'AALLISON'
EXEC sp_grantdbaccess 'AALMAGUE'
EXEC sp_grantdbaccess 'AAMBUSH'
EXEC sp_grantdbaccess 'AAPONTE'
EXEC sp_grantdbaccess 'AARANA'
EXEC sp_grantdbaccess 'AARONSON'
EXEC sp_grantdbaccess 'AAUERBAC'
It runs through and says I have over 2000 rows affected, but when I look in the database, after refreshing, none of these users are added. I ran the script while connected to the database in question....and if I were to just run one of those statements, i.e. (exec sp_grantdbaccess 'AALLISON', it would work....just doesn't seem to be working en masse...which is what I need. The exact script that I ran was:
Select 'EXEC sp_grantdbaccess ''' + [Name] + '''' From master..syslogins
Select 'EXEC sp_AddRoleMember ''Elite'',''' + [Name] + '''' From master..syslogins
Thanks for your help so far, I appreciate it!
EXEC sp_grantdbaccess 'AABRAHAM'
EXEC sp_grantdbaccess 'AALLISON'
EXEC sp_grantdbaccess 'AALMAGUE'
EXEC sp_grantdbaccess 'AAMBUSH'
EXEC sp_grantdbaccess 'AAPONTE'
EXEC sp_grantdbaccess 'AARANA'
EXEC sp_grantdbaccess 'AARONSON'
EXEC sp_grantdbaccess 'AAUERBAC'
It runs through and says I have over 2000 rows affected, but when I look in the database, after refreshing, none of these users are added. I ran the script while connected to the database in question....and if I were to just run one of those statements, i.e. (exec sp_grantdbaccess 'AALLISON', it would work....just doesn't seem to be working en masse...which is what I need. The exact script that I ran was:
Select 'EXEC sp_grantdbaccess ''' + [Name] + '''' From master..syslogins
Select 'EXEC sp_AddRoleMember ''Elite'',''' + [Name] + '''' From master..syslogins
Thanks for your help so far, I appreciate it!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works! I misunderstood you a while back and didn't realize that I had to paste the results back into a query window and run again. My bad...thanks so much for the help, that saves me a ton of time!! 500 points to you!
Glad I could help, sorry I didn't make it clearer :)
Select 'EXEC sp_grantdbaccess ''' + [Name] + '''' From Master..SysLogins
Select 'EXEC sp_AddRoleMember ''Elite'',''' + [Name] + '''' From Master..SysLogins
Please not that sp_grantdbaccess doesn't take a databasen name as an argument, just the login name (and optionally the db user name)
I guess this illustrates why its a good idea to use windows groups (if possible), as then you only have to apply it to one object - the windows group login.