October 29, 2008 at 12:32 am
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.
October 29, 2008 at 5:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2008 at 5:55 pm
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.
October 30, 2008 at 5:41 am
abhinav_k13 (10/29/2008)
Thanks a lot Grant. That was really helpful. A followup question to this isOnce 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply