November 3, 2009 at 5:52 pm
Dears Sirs....
I have the following issue...
I backed up a database from a Production Environment and restore it in a Development environment and also in a Test environment...
Some Developers and Programmers had acess (db_ddladmin, db_datawriter, db_datareader and others...), and after the restore, some of them can't acess this database anymore... 'cause the permissions are lost..
I tryed to use the "sp_change_users_logins " but it didn't work...
The question is: Is there any way to export the users from a especific database and them import them in another database exactly equal to the one where they were exported from ???
Thank you very much...
November 4, 2009 at 5:01 pm
Hi,
Just curious - do these developers and programmers have db_ddladmin role membership on production system?
If they use Windows authentication, the sp_change_users_login will not work.
Maybe permissions set on production database are just different from the permissions you have on DEV environment. You should script effective permissions on DEV environment and reapply them to the restored database from production.
Regards
Piotr
...and your only reply is slàinte mhath
November 5, 2009 at 4:22 am
Piotr.Rodak (11/4/2009)
Hi,Just curious - do these developers and programmers have db_ddladmin role membership on production system?
If they use Windows authentication, the sp_change_users_login will not work.
Maybe permissions set on production database are just different from the permissions you have on DEV environment. You should script effective permissions on DEV environment and reapply them to the restored database from production.
Regards
Piotr
November 5, 2009 at 4:26 am
Piotr.Rodak (11/4/2009)
Hi,Just curious - do these developers and programmers have db_ddladmin role membership on production system?
If they use Windows authentication, the sp_change_users_login will not work.
Maybe permissions set on production database are just different from the permissions you have on DEV environment. You should script effective permissions on DEV environment and reapply them to the restored database from production.
Regards
Piotr
Nope...
They didn't have any access to the Production environment... and, they use Windows autentication...
I think the best option, would be to script the permissions of all users for a specific database, and then apply it after restore...
I just didn't find out how to do it....
Can you help me ???
Best Regards..
Edvaldo Castro
November 5, 2009 at 4:58 am
You're going to have to write up a script.
The way I do it is to have my restores created in jobs. After the restore step, I have a script that does the user login fix in a cursor (yes, the evil cursor) so it automatically cycles through all the logins currently on the db.
After the cursor deallocates, the CREATE USER and sp_addrolemember run in that step.
It's really that simple. And if the logins don't exist at all in the server, you'll have to use the CREATE LOGIN before you can use the CREATE USER.
November 5, 2009 at 5:52 am
Brandie Tarvin (11/5/2009)
You're going to have to write up a script.The way I do it is to have my restores created in jobs. After the restore step, I have a script that does the user login fix in a cursor (yes, the evil cursor) so it automatically cycles through all the logins currently on the db.
After the cursor deallocates, the CREATE USER and sp_addrolemember run in that step.
It's really that simple. And if the logins don't exist at all in the server, you'll have to use the CREATE LOGIN before you can use the CREATE USER.
For this specific issue, I had to fix them one by one... 'cause it was too important for the company... but, I always have to performe this task...
I'll try to develop a script to help me with that...
Thank you very much...
November 5, 2009 at 6:01 am
One by one? Ah, you need to be more creative. @=)
If you can only "fix" certain users, try putting the specific logins in a table on a DBA only database and run the Fix Users cursor against that.
Do you know how to do dynamic SQL?
November 5, 2009 at 6:27 am
Brandie Tarvin (11/5/2009)
One by one? Ah, you need to be more creative. @=)If you can only "fix" certain users, try putting the specific logins in a table on a DBA only database and run the Fix Users cursor against that.
Do you know how to do dynamic SQL?
I try to be as creative as I can... but I'm only as DBA only for about 6 months.. lol
When I said "fix", I meant sometimes create login, sometimes use the stored procedure "sp_change_users_login" and things like that...
and.. I don't know dynamic SQL yet...
Thanks for your help Brandie...
November 5, 2009 at 6:34 am
First look up CREATE LOGIN, CREATE USER and sp_addrolemember in BOL (Books Online). This will give you all the syntax you need.
Dynamic SQL is the act of putting SQL into a variable and concatenating it with other variables or results from other queries. The SQL itself changes depending on the values of those results/other variables.
Here's the basic cursor for fixing all SQL users in SQL 2k5. The one thing you have to be aware of is if your db is owned by a SQL Only login, you either have to change the database owner to a domain login or exclude the SQL Only login from the fix. Otherwise, the cursor will error out.
Look up the cursor in BOL so you understand what it's doing. To call logins from a specific table, just change the initial SELECT statement.
/*-----------------------------------------------------------------------------
After restoring a database, fix users that have a corresponding login on this server
-----------------------------------------------------------------------------*/
-------------------------------------------------------------------------------
-- Declarations
-------------------------------------------------------------------------------
DECLARE @User_Name varchar(255), -- User Name
@Adhoc_SQL nvarchar(2000) -- Used with sp_ExecuteSQL
-------------------------------------------------------------------------------
-- Declare Cursor
-- Only Users that need fixing that also have a valid login on this server
-------------------------------------------------------------------------------
DECLARE User_Cursor CURSOR FOR
SELECT DP.[name]
FROM sys.database_principals DP
INNER JOIN sys.server_principals SP
ON DP.[name] = SP.[name]
WHERE DP.type_desc = 'SQL_USER'
AND (DP.sid IS NOT NULL AND DP.sid <> 0x0)
AND SUSER_SNAME(DP.sid) IS NULL
--AND DP.[name] <> 'DB_Owner_SQLLogin'
-------------------------------------------------------------------------------
-- Open Cursor of Users
-------------------------------------------------------------------------------
OPEN User_Cursor
FETCH NEXT FROM User_Cursor
INTO @User_Name
-------------------------------------------------------------------------------
-- Process Each User and issue Fix command via Sp_Change_Users_Login
-------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build Command String
SET @Adhoc_SQL = 'EXEC sp_change_users_login '+
'@Action = ''Auto_Fix'', ' +
'@UserNamePattern = ''' + @User_Name + ''''
-- Display Command issued
print @Adhoc_SQL
-- Execute Command
EXEC sp_ExecuteSQL @Adhoc_SQL
-- Get the next Row
FETCH NEXT FROM User_Cursor
INTO @User_Name
END
-------------------------------------------------------------------------------
-- Cursor Cleanup
-------------------------------------------------------------------------------
CLOSE User_Cursor
DEALLOCATE User_Cursor
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply