SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL server accounts for development


SQL server accounts for development

Author
Message
abhinav_k13
abhinav_k13
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97563 Visits: 33014
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
abhinav_k13
abhinav_k13
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97563 Visits: 33014
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search