July 30, 2011 at 8:54 am
Hi,
I have a database in SQL Server 2005 with so many db users and lots of permissions with column level also. My question is, if I do db refresh on another server. What about the all permissions column level permissions as well as. Will those permissions be retained on target server ? If no then how can I retain those all permissions as well as column level permissions on target server ? Does Microsoft provide any solutions for this kind of situations ?
Thanks in advance.
July 30, 2011 at 9:59 am
The Users and permissions to the Database will be retained with the refresh.
You will need to transfer the Logins for each Database User.
You will also need to fix Orphan Users each time you restore the Database.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 30, 2011 at 10:00 am
Users are stored in the database (see: sys.database_principals). With that said, if you make sure the logins are recreated with the same SID on the new server - when you restore the database the users in the database will not be orphaned and all permissions set at the database level will be available.
To make sure you create the logins appropriately, lookup sp_help_revlogin. Use this procedure to create the logins on the new server after restoring the databases.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2011 at 10:11 am
Good point if you create the Login with the same SID then you will not have orphaned Users.
If you already have a lot of Logins on the target server and the SIDs are already taken then you creating new logins with the same SID as the User will be a problem and may be easier to fix orphaned Users.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 30, 2011 at 11:11 am
ok fine, but what about those database users who were already there on target database and now database is overwritten.
Like : Server 1 -- database1--users1,2,3,4 took the backup of database1
target server2--database2--users5,6,7,8
if I overwrite the backup of database1 on database2 the users5,6,7,8 will also be overwritten, is there any way to retain these users also.
July 30, 2011 at 11:24 am
beejug1983 (7/30/2011)
ok fine, but what about those database users who were already there on target database and now database is overwritten.Like : Server 1 -- database1--users1,2,3,4 took the backup of database1
target server2--database2--users5,6,7,8
if I overwrite the backup of database1 on database2 the users5,6,7,8 will also be overwritten, is there any way to retain these users also.
When you say overwrite don't you mean restore the current database with a backup?
Initially you stated "refresh", an assumption was made that you were referring to a restore from a production backup. Is that the case?
Also why are your refreshing the data to Server 2? Is the a Development or QA box?
You want to use the same database name if you are going to be accessing it from code, e.g. .NET, ASP, etc. so that you can minimize the number of changes.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 30, 2011 at 11:31 am
I am restoring from production to development. On development, database is already created and users are working on it. But the moment when i restore database backup of production to development environment. The existing db users get overwritten.
July 30, 2011 at 11:38 am
I am using the same database name on both environment.
July 30, 2011 at 11:49 am
You should have the same users in your Development Environment as your Production Environment.
That way your Development Environment mimics your Production Environment.
If that is not the case you may want to reconsider how you handle users.
I assume that you have scripted all of the permissions and have stored them is a source control program?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 1, 2011 at 2:39 pm
Welsh Corgi (7/30/2011)
You should have the same users in your Development Environment as your Production Environment.That way your Development Environment mimics your Production Environment.
If that is not the case you may want to reconsider how you handle users.
I assume that you have scripted all of the permissions and have stored them is a source control program?
This is not neccesarily a good idea. I've worked in the banking environment most of my DBA career, and we almost never had the same users in DEV, QA and PROD. The risk of developers or testers getting to clients live data was just to risky.
It is however easy to create scripts that extract all the current permissions for later recreation. Here are two i've used in different situations. The first one generates all database role membership and the second all object level permissions. I've never needed to manage permissions on a column level, as I would normally have managed that via views which I feel are easier to manage.
Script user database roles:
select 'sp_addrolemember ' + char(39) + DP.name + char(39) + ',' +char(39) + DP2.name +char(39) +'
go'
from sys.database_principals DP
join sys.database_role_members DRM on DP.principal_id = DRM.role_principal_id
join sys.database_principals DP2 on DRM.member_principal_id = DP2.principal_id
where DP.type = 'R'
and DP2.principal_id > 4
and DP2.name = 'Username'
Script Object level permissions (I actually got this from another source, but I can't remmber where)
set nocount on
declare @OxType char(2)
declare @PActAdd smallint
declare @PActMod smallint
declare @OName sysname
declare @UName sysname
declare @CmdTab table (
UName sysname,
OName sysname,
CmdStr nvarchar(400))
declare CurPermissions cursor for
select o.xtype, p.actadd, p.actmod, o.name, u.name
from sysobjects o
join syspermissions p on o.id = p.id
join sysusers u on u.uid = p.grantee
where (issqlrole = 1 or hasdbaccess = 1)-- and gid != 0 -- Make sure these conditions are correct
and u.name != 'dbo'and u.name != 'public'
order by u.name, o.name
open CurPermissions
fetch next from CurPermissions
into @OxType, @PActAdd, @PActMod, @OName, @UName
while @@fetch_status = 0
begin
if @OxType in ('V','U')
begin
if @PActAdd != 0
begin
insert into @CmdTab select @UName, @OName,
case
when @PActAdd = 1 then 'grant select '
when @PActAdd = 2 then 'grant update '
when @PActAdd = 3 then 'grant select, update '
when @PActAdd = 8 then 'grant insert '
when @PActAdd = 9 then 'grant select, insert '
when @PActAdd = 10 then 'grant update, insert '
when @PActAdd = 11 then 'grant select, update, insert '
when @PActAdd = 16 then 'grant delete '
when @PActAdd = 17 then 'grant select, delete '
when @PActAdd = 18 then 'grant update, delete '
when @PActAdd = 19 then 'grant select, update, delete '
when @PActAdd = 24 then 'grant insert, delete '
when @PActAdd = 26 then 'grant update, insert, delete '
when @PActAdd = 27 then 'grant select, update, insert, delete '
end
end
if @PActMod != 0
begin
insert into @CmdTab select @UName, @OName,
case
when @PActMod = 1 then 'DENY select '
when @PActMod = 2 then 'DENY update '
when @PActMod = 3 then 'DENY select, update '
when @PActMod = 8 then 'DENY insert '
when @PActMod = 9 then 'DENY select, insert '
when @PActMod = 10 then 'DENY update, insert '
when @PActMod = 11 then 'DENY select, update, insert '
when @PActMod = 16 then 'DENY delete '
when @PActMod = 17 then 'DENY select, delete '
when @PActMod = 18 then 'DENY update, delete '
when @PActMod = 19 then 'DENY select, update, delete '
when @PActMod = 24 then 'DENY insert, delete '
when @PActMod = 26 then 'DENY update, insert, delete '
when @PActMod = 27 then 'DENY select, update, insert, delete '
end
end
end
if @OxType in ('P','FN','IF')
begin
if @PActAdd != 0
begin
insert into @CmdTab select @UName, @OName,
case
when @PActAdd = 1 then 'grant select '
when @PActAdd = 32 then 'grant EXEC '
end
end
if @PActMod != 0
begin
insert into @CmdTab select @UName, @OName,
case
when @PActMod = 32 then 'DENY EXEC '
end
end
end
fetch next from CurPermissions
into @OxType, @PActAdd, @PActMod, @OName, @UName
end
close CurPermissions
deallocate CurPermissions
select CmdStr + 'on ' + OName + ' to [' + UName + ']
go' from @CmdTab order by UName,OName
As with all scripts, please test before you commit yourself.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 1, 2011 at 5:11 pm
Leo.Miller (8/1/2011)
Welsh Corgi (7/30/2011)
This is not neccesarily a good idea. I've worked in the banking environment most of my DBA career, and we almost never had the same users in DEV, QA and PROD. The risk of developers or testers getting to clients live data was just to risky.
Do you use Windows Authentication?
How does your script handle logins?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 1, 2011 at 5:40 pm
Welsh Corgi (8/1/2011)
Leo.Miller (8/1/2011)
Welsh Corgi (7/30/2011)
This is not neccesarily a good idea. I've worked in the banking environment most of my DBA career, and we almost never had the same users in DEV, QA and PROD. The risk of developers or testers getting to clients live data was just to risky.If you are using Windows Authentication how do you handle it?
Windows Authentication for users always, and some apps needed SQL Auth, but it doesn't matter which mode you use. The principle is the same: Never allow developers to have accounts on a production server. If they don't have the logon rights, then delete any orphaned users from the database.
We made sure that all applications on Prod, QA, Test and Dev use their own accounts so even if you accidently point one environment at another the logon will fail. I've seen this done when the upgrade was packaged from QA with the config file included.
For applications the account typically had an environment factor built into the name: Domain\MyApp_Prod_Acc or Domain\MyApp_QA_Acc.
When developers need access to another environment for debugging or the like, we were assigning their account temportary access, with a batch process configured to automatically remove their access after 6 hours. An alternative I've seen at one bank and an airline company, was all users who needed access to other environments were given a second, limited domain account with the appropriate rights, typically Domain\MyNormalDomainName_Admin. They then used RDP to connect to the prod environment.
At one company after we introduced the stict seperation of accounts (with much kicking and screaming I must admit) callouts for issues dropped over the next 6 months from 2-3 a night, to 1-2 a month as fewer mistakes were made and better testing was enforced because you couldn't just "go on the live machine and fix things". In the end everyone was getting a lot more sleep and spending less time on the carpet. Everyone was also a lot happier.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply