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

Setting Database password and permission Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2008 4:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2008 7:37 AM
Points: 3, Visits: 5
Hi,
I would like to know if it is possible to completely restrict users from performing all kinds of operations on a particular database in SQL Server. It includes restricting everything from updating a table to script generation. I know that it is possible to set up password for a database in SQL Server 2005, but is it possible in SQL Server 2000?
Anyway, if I did set password to a database, how do we access the database using our web application. What should we set up in the config file of our application in Tomcat server.
Please help me with this even though the question might look simple.
Thanks :)
Post #481400
Posted Tuesday, April 8, 2008 9:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 33,051, Visits: 15,160
Security is fairly granular in SQL Server 2005. You can not grant rights or deny rights for many operations.

If you are more specific in your questions, we can help. There is no overall "database password". You have a login with a password and that maps to a user in a database, which has rights.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #481652
Posted Tuesday, April 8, 2008 1:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2008 7:37 AM
Points: 3, Visits: 5
Ok. Now, I would like to know if I can restrict unauthorised users from doing all kinds of operations like generating scripts, updating or deleting records, deleting tables even after they have entered the database using a password, which might have reached their hands by some mistake. It is possible to prevent deleting of records using an instead of trigger as I read it. But, I am looking for a solution where in a user would have to reauthorise to perform any of these operations. i would like to know about the solution in SQL server 2000. And also, the password protection for each database is only in the SQL server 2005 compact edition. Can anybody please elaborate on this edition?
Thanks
Post #481836
Posted Tuesday, April 8, 2008 5:28 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 4,065, Visits: 5,273
If you haven't mapped a login to a user in a database and granted it access to the database, the login can't even open the database. Even when there is a user in a database it can only do what you've given it permission to do. See "permissions [SQL Server]" in BooksOnLine.

This works the same in SQL 2000 as it does in SQL 2005.


Greg
Post #481942
Posted Wednesday, April 9, 2008 12:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2008 7:37 AM
Points: 3, Visits: 5
Thanks a lot for your all your help. Now the requirement seems to have changed and we don't need to do all what I had asked about. Now, I would like to know how we can take a backup of the database over the internet automatically. We could achieve it over a LAN where in we make the .bak file save in a shared folder in different machine in the LAN. How can we achieve it over the internet? What is the difference in approach or procedure?
Post #482070
Posted Wednesday, April 9, 2008 6:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 33,051, Visits: 15,160
You can't take a backup over the Internet. SQL Server is very intolerant of delays in network traffic and this wouldn't work.

You can back up to disk and then copy the file over the Internet. Some FTP or Secure-FTP would be the best way to manage this.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #482256
Posted Friday, December 2, 2011 10:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 4, 2011 9:11 PM
Points: 3, Visits: 15
Dear Guns

As you have specified in your post, setting individual database password is possible in sql server2005.
Will it be able to protect your db even if someone copies the mdf file and then tries to attach it to his system using Windows authorisation or sa login.

If yes, Kindly share the procedure.
My requirement in detail is as below

Hi Guys

Please help me with this

In Short:
I need to lock a sql database with my own password, such that it can't be opened through windows authentication mode also.


Detailed:

I have a small applicattion that uses sql server2005 database.
So far I used to supply the application with online database and all sort of security so that no competitor of mine can steal it.

Now for one of my clients, I need to install the application to work in offline mode. i.e. I need to put the database also on his local machine.
Now If someone wants to copy the db and use it develop his own application similar to mine, He can follow following steps

1) If I only install sql express edition and provide mdf ldf files,
He can copy it and attach to his another machine with Windows authentication mode and see my stored procedures and table schemas

2) If I install sql server and create a new user say LPS with a pwd and delete all the other users including windows authentication .

He cannot copy the db as its attached to sql server and cant open sql server to detach this and then copy,
But if he uninstalls sql from his machine and re installs it, he will have sa password reset to whatever he wants and then use it.

Post #1215779
Posted Saturday, December 3, 2011 4:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 2,013, Visits: 1,584
hitechimp (12/2/2011)
...


Please reply here for hitechimp's question.
http://www.sqlservercentral.com/Forums/Topic1215777-1550-1.aspx


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1215804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse