June 9, 2009 at 3:50 am
I am having SQL Server 2005 installed on Database server. This will be accessed by 10 different teams to create their databases & access them thereafter. I have created separate logins for each team & given them dbcreator role. The teams can access a shared folder to put the backup files on Server.
Here is THE problem i am experiencing.
The team sql users can not restore a database as they don't have file permissions on Server.
So i give one team member (windows auth) dbcreator rights & allow him access to shared folders to restore databases.
Now the windows user can restore the database.
This doesn't solve the problem as windows user can not change the newly created db's accessibility to teams sql user.
And I do not want to give this windows user as sys admin for this purpose
Is there any guideline on how to deal with such an issue
Any help would be very much appreciated!
- Sharjeel
June 9, 2009 at 6:35 am
I support quite a few development teams. I usually take charge of creating their databases, setting up backups & restores and then, depending on the team, their level of expertise, etc., I give them permissions to DDL & Security within the database. Giving them permission to create & manage databases directly just leads to disaster since their expertise is in development, not database management.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 9, 2009 at 7:15 am
I think the preferred way to deal with this situation is to create Custom roles for both the server and the databases.
However, I don't think, its a good practice to give db users the rights to create database.
You can create database for each team and then create custom roles for each group.
As of now, I think this is way to deal with this situation... I had done the same in my previous assignment.
Let me know, if you find some other way to fix this issue.:-)
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
June 10, 2009 at 12:51 am
Thanks a lot for you help!!
Unfortunately i had to allow the developers for creating & restoring databases.
They receive many bak files daily from the customers on support issues to deal with.
And I cant give this much time for create/restore db activity everyday.
So we are back to square one!! 🙂
June 10, 2009 at 1:51 am
I would consider setting the dev team up with their very own sandbox server.
You can initially install and configure SQL Server for them. After that the Dev team have to run with it, with your guidance of course.
If the dev team need to play at being DBA's then it may be in your interest to train them up, perhaps to a very basic standard.
June 10, 2009 at 2:09 am
We use the same attitude Grant Fritchey posted.
To support your "support teams" requirements to be able to restore any backup on any given time and database, I would provide them a separate instance to do just that.
(don't mix with your regular dev/QA instances)
Off course the bak files need to be on a shared resource you service account can read.
You could provide a DBA_Request_Restore table to which they are allowed to insert and have a job handle the actual restores using the sqlagent service account.
This DBA_Request_Restore table would contain columns like:
- Target_DBName
- Source_Bak_Filename
- Datetime and user info of the requestor
- Approval status. ( by team leader )
- execution data
Schedule that job e.g. every 30mins.
Have the restore statement build dynamically !
maybe this can be a starting point:
/* generate RESTORE DATABASE from backup file */
Declare @BU_DbName sysname
Declare @Restore_DbName sysname
Declare @BackupPath NVarchar(2000)
Select @BU_DbName = '2bModified' --> MODIFY
, @BackupPath=''
, @Restore_DbName = ''
if coalesce(@Restore_DbName,'') = ''
begin
set @Restore_DbName = @BU_DbName + '_RESTORED'
end
Declare @DataPath NVarchar(2000)
Declare @LogPath NVarchar(2000)
/* Are there default Data/Log folders defined for this instance ? You realy should do this !*/
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DataPath OUTPUT
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @LogPath OUTPUT
If isnull(@BackupPath,'') =''
begin
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupPath OUTPUT
end
Declare @Filename varchar(500)
Set @Filename = @BackupPath + '\' + @BU_DbName + 'Full.bak'
if 1 = 1
begin
print 'Restore_DbName: [' + coalesce(@Restore_DbName,'???')+ ']'
print 'DataPath: [' + coalesce( @DataPath , '**UNKNOWN**') + ']'
print 'LogPath: [' + coalesce( @LogPath , '**UNKNOWN**') + ']'
print 'BackupPath: [' + coalesce( @BackupPath , '**UNKNOWN**') + ']'
print @Filename
end
create table #tmpFilelist -- generated from BOL Sep 2007
( LogicalName nvarchar(128) ,
PhysicalName nvarchar(260) ,
[Type] char(1) ,
FileGroupName nvarchar(128) ,
[Size] numeric(20,0) ,
[MaxSize] numeric(20,0) ,
FileID bigint ,
CreateLSN numeric(25,0) ,
DropLSN numeric(25,0) NULL ,
UniqueID uniqueidentifier ,
ReadOnlyLSN numeric(25,0) NULL ,
ReadWriteLSN numeric(25,0) NULL ,
BackupSizeInBytes bigint ,
SourceBlockSize int ,
FileGroupID int ,
LogGroupGUID uniqueidentifier NULL ,
DifferentialBaseLSN numeric(25,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
IsReadOnly bit ,
IsPresent bit)
Insert into #tmpFilelist
exec ('restore filelistonly from disk=N''' + @Filename + '''')
Select case FileID when 1 then ' Restore database [' + coalesce(@Restore_DbName,'???') + ']
from disk=''' + @Filename + '''
with MOVE N'''+ LogicalName + ''' TO N''' + @DataPath + rtrim(replace( substring( PhysicalName, (datalength(PhysicalName)/2) - charindex('\', reverse(PhysicalName)) + 1 , charindex('\', reverse(PhysicalName)) ), @BU_DbName, @Restore_DbName ) ) + ''''
else
', MOVE N'''+ LogicalName + ''' TO N''' + rtrim( case [Type] when 'L' then @LogPath else @DataPath end + replace( substring( PhysicalName, (datalength(PhysicalName)/2) - charindex('\', reverse(PhysicalName)) + 1 , charindex('\', reverse(PhysicalName)) ), @BU_DbName, @Restore_DbName ) ) + ''''
end
from #tmpFilelist
order by FileID ;
drop table #tmpFilelist;
ps: Be sure to add something like a db-retention periode to ensure they are cleaned up !
pss: space may be an issue (because you need to be able to allocate the original file sizes the db backup has been created from)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply