May 7, 2010 at 9:46 am
Hi Folks
I have several developers with there own servers that accesse each of their databases on another server. They all need to be able to do exports and imports on their own servers for their own databases. I understand that they need DBO privilege to do the exports and imports.
Can someone tell me the easiest way I can accomplish this for all these individual developers ?
i know SSMS is an option, but i would like to use some tsql commands to accomplish it and possible use some dynamic tsql to create the statements
Thanks
Jim
May 7, 2010 at 12:13 pm
Is there an active directory group that they are all part of, or will you be pulling the users from an active directory call?
May 7, 2010 at 12:19 pm
For just one user or group you could run something like the following:
[Code="sql"]
Declare @Username sysname
Set @Username = 'Domain\Username'
Declare @DB sysname
,@SQL Varchar(8000)
Declare DB Cursor Static
for Select name
From master..sysdatabases
Where name not in ('master','model','msdb','tempdb')
and databaseProperty(Name,'IsOffline') = 0
and databaseproperty(name,'IsInLoad') = 0
and databaseproperty(name,'IsInRecovery') = 0
and databaseproperty(name,'IsReadOnly') = 0
Open DB
While 0=0
Begin
Fetch Next
From DB
into @DB
if @@fetch_Status <> 0
Break
Set @sql = 'Use [' + @DB + ']
If not exists(Select * from sys.database_principals where name = ''' + @Username + ''')
Begin
Create User [' + @Username + '] From login [' + @Username + ']
Exec sp_addrolemember ''db_owner'',''' + @Username + '''
End
'
--Print @sql
Exec(@SQL)
End
Close DB
Deallocate DB
[/Code]
May 7, 2010 at 12:37 pm
I believe they all have domain accounts that they use to log on to their respective servers
May 7, 2010 at 12:40 pm
can you explain to me just what this does ?
May 7, 2010 at 12:55 pm
It places an active directory account in the db_owner role for each of the databases on a server except those that are in the in list ('msdb','master','etc').
May 7, 2010 at 1:30 pm
Hi Toby
well i would rather not execute anything unless i can see what it does
does this actually do it instead of generating the sql to do it
is 'Domain\Username' suppose to be replaced by an account i have ?
Thnaks
Jim
May 7, 2010 at 2:48 pm
Yes, at the top you would just replace
Set @Username = 'Domain\Username'
with the actual account names you wish to place in the db_owner role of the database. However, I am not sure if this is what you are trying to accomplish. Are all the users you want to place in the db_owner role for all your databases known?
May 7, 2010 at 2:56 pm
Hi Toby
well..let me start over
i have a sql server with many databases
i have several developers with their own server (1 server = 1 developer)
they connect from these developer servers with their active directory logon
to each of their databases on the sql server
they want to be able to export and import their own data from their databases to their servers
i need to give them permissions to do so
i figured it was just a simple grant of the dbcreator role, but not sure
hope that explains it
Thanks for assisting
Jim
May 7, 2010 at 3:00 pm
Jim,
It's still not clear. You're saying you have developers and each has their own server? Do you mean that the developers have their own instance locally on their machines? And then to where are they exporting data?
May 7, 2010 at 3:06 pm
Hi Steve
each developer has their own virtual server that they log on to for developement
then then connect to a SQL Server with SQL SERVER 2005 databases on it
they want to export / import data down to their own server
Jim
May 7, 2010 at 3:08 pm
More Info:
they are using sharepoint on their own servers
their sharpoint databases reside on the SQL server 2005 server
May 7, 2010 at 3:12 pm
OK, if I understand this.
You have a master SQL Server instance, let's call it Server A. This has various databases on it. You have a developer (DevA, DevB) and each has a VM with SQL Server installed on it (VSQL1, vSQL2). You want DevA to be able to connect to ServerA and copy all the data in a database, or the entire database, down to vSQL1. And DevB to do the same thing from ServerA to VSQL2.
There are a few ways you can do this. If it's rare, or fairly rare, giving them a backup of the database on ServerA is pretty easy to do and they can restore this to VSQL1 or VSQL2.
You use BIDS and have them build a Transfer Database Task package and use that. Or they can easily use the Import/Export wizard to move data once you have a schema. That gets to be a pain if you have objects that are changing more often.
In terms of rights, you could grant the db_backupOperator role to a group, and then put the developers in the group. That's what I'd recommend if you want to do backups. Create an AD group, add the devs to it, then add that group to ServerA with the db_BackupOperator role in each database. You could also grant them the db_owner role as well.
May 7, 2010 at 3:20 pm
Hi Steve
that sounds do-able
first i believe they do not want to do backups, i do those on the sql server
i think they want to be able to do exports and imports of their data
so if they already have db_owner, then they can do the exports ?
they already have that role
Thanks
Jim
May 8, 2010 at 3:13 pm
JC-3113 (5/7/2010)
I understand that they need DBO privilege to do the exports and imports.
what makes you think that??
if they are importing data and the database objects already exist (tables,etc) they simply need insert,update on the destination and select on the source!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply