March 18, 2010 at 3:29 am
Hi,
We would like to refresh our dev database from prod. Before that we want the users to have the same permissions after the refresh also. Because some of users have read only on prod and read-write permisisons on dev. So we want user to have the same permission after the refresh also. ie. read-write. Is there any script for that. Permisisons are given through roles and individual like grant select on tbl to user... also.
Thanks
Chandra Mohan N
March 18, 2010 at 3:50 am
newbie2sql (3/18/2010)
Hi,We would like to refresh our dev database from prod. Before that we want the users to have the same permissions after the refresh also. Because some of users have read only on prod and read-write permisisons on dev. So we want user to have the same permission after the refresh also. ie. read-write. Is there any script for that. Permisisons are given through roles and individual like grant select on tbl to user... also.
Thanks
Chandra Mohan N
It goes without saying that your users will have to have the relevant logins on the dev SQL server, but assuming your dev environment is a clone domain the AD users SID's will be different (they wont match those help in the restored database users).
So you will need to run this, on the restored database:
select 'alter user [' + b.name + '] with login = [' + b.name + ']'
from .dbo.sysmembers a
join .dbo.sysusers b on a.memberuid = b.uid
where b.islogin=1 and b.name <> 'dbo'
group by b.name
order by b.name
Copy out the output and run against the same database and yoou should be fine.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 18, 2010 at 3:59 am
If you have SQL users rather than windows authenticated users see:
http://support.microsoft.com/kb/246133 this will enable you to script out all users, permissions and passwords and apply them to the new server.
March 18, 2010 at 6:33 am
Thanks for the reply.
Solutions which you had given works only for user synchronization i.e. fixing the orphan users. Here I am not asking about orphan users and fixing them. Let me explain the case clearly.
Suppose we have a user myusr who has read permissions through the role rl_read on prod server and write permisisons through the role rl_readwrite on dev server. When i restore the prod backup on the dev server and if i fix the orphan users I get only read permissions when the orphan user is fixed he is automatically mapped to rl_read dbrole which I dont want.
I want to script out all the users, their roles and permissions given to the role before refresh so that i can reapply the same script after the refresh.
Thanks
Chandra Mohan
March 18, 2010 at 2:08 pm
A nice free tool for this is http://www.idera.com/Products/Free-Tools/SQL-permissions/ You run it before the restores on you dev\qa\stage box to get the permissions, it also scripts out the logins, but given they are already there, you wouldn't need that piece. You may need to clean out the permissions granted on the restored DBs, given they will be the production permissions, and then run the saved script. You might also need to sync up the sids of the orphaned users using sp_change_users_login.
Andrew
March 18, 2010 at 2:49 pm
There are ton of them, here is one.
http://www.sqlservercentral.com/Forums/Topic715469-359-1.aspx
March 18, 2010 at 4:24 pm
try this
--uncomment the where clause and specify a database user
--to get permissions for a single account
USE yourdbhere
SELECT
'Permission "' + CONVERT(NVARCHAR(10), permission_name) COLLATE Latin1_General_CI_AS + '" "' +
case CONVERT(NVARCHAR(10), state_desc) COLLATE Latin1_General_CI_AS
when 'DENY' then 'DENI'
else CONVERT(NVARCHAR(10), state_desc) COLLATE Latin1_General_CI_AS
end
+ 'ED'' ' + 'on object "[' + sch.name + '].[' + obj.name + ']" to principal "[' + dbpr.name + ']"'
FROM sys.database_permissions dbpe
INNER JOIN sys.database_principals dbpr ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
--where (dbpr.name = 'dbusernamehere')
ORDER BY dbpr.name, obj.name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 19, 2010 at 3:44 am
Read below post
http://support.microsoft.com/kb/246133
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 19, 2010 at 4:26 am
Hi Bhuvnesh,
One humble request from users like you. Please dont reply if you dont understand the question or dont know the answer for god's sake dont reply with incorrect answers. I clearly mentioned that I dont want for fixing orphan users and you are giving me the same again.
Thanks
Chandra Mohan
March 19, 2010 at 4:28 am
Sorry, I thought that the link might help you, as you can script out the users and apply them after having data refresh.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 19, 2010 at 6:14 am
hi
But you do not have to be rude like this.. you could have just ignored his post.. thats how people starts here... At the start itself no body answers correctly. its your duty to analyze and implement the solutions.
March 19, 2010 at 6:22 am
vmssanthosh (3/19/2010)
hiBut you do not have to be rude like this.. you could have just ignored his post.. thats how people starts here... At the start itself no body answers correctly. its your duty to analyze and implement the solutions.
i am completely agree with this , it is actually demotivating forum members to post there expected results/answer.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 19, 2010 at 6:59 am
Bhuvnesh (3/19/2010)
vmssanthosh (3/19/2010)
hiBut you do not have to be rude like this.. you could have just ignored his post.. thats how people starts here... At the start itself no body answers correctly. its your duty to analyze and implement the solutions.
i am completely agree with this , it is actually demotivating forum members to post there expected results/answer.
Children children. Now lets all play nice. Everyone has to start somewhere and not all posters have time to read each post on a thread and meticulously analyse the intent or supposed intent. Ultimately the ownes falls on the original postee to sift through the responses.
We can only offer advise, its up to the person in question if he/she wishes to take it. This isn’t a product, it doesn’t come with a guarantee and there is no customer service.
Going back to an older post, I would give Idera a go. I use their tools all the time and they work really well.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 19, 2010 at 6:31 pm
i dont see whats wrong with my script?? :ermm:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 19, 2010 at 8:56 pm
vmssanthosh (3/19/2010)
hiBut you do not have to be rude like this.. you could have just ignored his post.. thats how people starts here... At the start itself no body answers correctly. its your duty to analyze and implement the solutions.
Hey Santhosh,
Do you think I am rude I dont think so. I had already mentioned in the post that I am not looking for solutions for fixing orphan users. But still Bhuvnesh had replied with the same solution which already some users had posted for fixing orphan users and I had replied that I am not looking fixing orphan users and mentioned my problem with an example. After reading the problem if the user again gives solution for fixing orphan users whats the use. Atleast if he cannot understand my problem I think he can understand that "I am not looking for fixing orphan users". I agree with your valid point nobody answers correctly but that does not mean answer the posts which you dont even understand or have no idea.
Thanks
Chandra
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply