Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL server accounts for development Expand / Collapse
Author
Message
Posted Wednesday, October 29, 2008 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 30, 2008 6:41 PM
Points: 2, Visits: 4
I am in charge of a small website which is connected to a database running SQL server 2005. The code on the server side was developed using the windows administrator account.
The website uses stored procedures for data optmizations and as long as the website and the database were on the same server, we had no problems running it. Now the database has been moved to a server where we dont have the admin username and password.
How can I create a username and grant it permission such that the website can run the stored procedures/modify tables etc under this user name.
Second question on this is. I realize our developer accounts are not setup in SQL server. How should I set these up such that each developer can create her/his own tables and stored procedures under the same database be able to access data and run queries/stored procedures etc.
Currently if each developer creates a stored procedure under her name, then the stored procedure shows up as username.stored procedure name.
Any help is appreciated.
Post #593406
Posted Wednesday, October 29, 2008 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 13,862, Visits: 28,258
You can set up the logins one of two ways. You can create a SQL login (not recommended) which has a name and a password. Better is to set up a login through windows authentication. This would be a login created on your domain. You can then put the password, encrypted, into the registry (one way, there are others) of the web server. Nice & safe.

It wasn't clear from your description, but you're not letting the developers create stuff on the production machine are you? That's bad. I'm assuming that your clients & customers would like to see their data stay intact. Develop on a different box.

As to how to set up the developers security, actually, that's not the issue. The way they're trying to create the procs is. Instead of :
CREATE PROCEDURE MyProcname

Have them include the schema (I'm assumming you want to use 'dbo'):
CREATE PROCEDURE dbo.MyProcName

Now it won't be owned by the individual developer any more.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #593499
Posted Wednesday, October 29, 2008 5:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 30, 2008 6:41 PM
Points: 2, Visits: 4
Thanks a lot Grant. That was really helpful. A followup question to this is
Once the developers create content on the dev server(functions/ stored procedures)which is currently integerated with the website running on the same machine under admin privileges, I need to migrate this to a production machine where the website will not be running under admin privileges.
What credentials should I provide for such a website such that it is able to execute the stored procedures/view tables etc in the database.

Second question
What is the best strategy to move newly created content over. By content I mean mostly tables, stored procedures, functions and data stored in the tables.
Since we will be updating the database monthly(the users will be entering data as well) I am not sure if we can overwrite the whole database from dev machine to production machine.
Post #594025
Posted Thursday, October 30, 2008 5:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 13,862, Visits: 28,258
abhinav_k13 (10/29/2008)
Thanks a lot Grant. That was really helpful. A followup question to this is
Once the developers create content on the dev server(functions/ stored procedures)which is currently integerated with the website running on the same machine under admin privileges, I need to migrate this to a production machine where the website will not be running under admin privileges.
What credentials should I provide for such a website such that it is able to execute the stored procedures/view tables etc in the database.


Short answer: It depends.

Longer answer: You need to give it access to the server. Then you need to connect that login to the server with a user in a database. I personally have the users belong to a role and then I grant permissions to execute procedures or read or write data to the role, never to any users. Other people do it different ways.

Second question
What is the best strategy to move newly created content over. By content I mean mostly tables, stored procedures, functions and data stored in the tables.
Since we will be updating the database monthly(the users will be entering data as well) I am not sure if we can overwrite the whole database from dev machine to production machine.


Again, it depends. The approach I've been using for years & years is to develop a migration script (or scripts), test it on a copy of production until I'm sure it moves everything I need it to move and doesn't blow up the production data and then I run the script against production. This means no ad hoc scripting on production, which is strictly a check against me, because, given the chance, I'll screw up somehow.

Generating this script is another pain point. You can do it manually. You can try doing it using Management Studio tools, or, I'd suggest getting a third party tool. Apex has a comparison utility that's supposed to be pretty good. There's another one called DBGhost that I've tested. It worked well and more importantly, worked out of source control. One of my favorites is still Red Gates SQL Compare. It works out of source control as well as database to database. If you really want to get into it though, you pick up a copy of Microsoft's Visual Studio Team System Database Edition (free if you already have a copy of Team System Developer Edition) and then build and deploy your database exclusively from source control, just like the developers do.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #594210
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse