Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting Database password and permission


Setting Database password and permission

Author
Message
guns_14
guns_14
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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 Smile
Steve Jones
Steve Jones
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: Administrators
Points: 52138 Visits: 19005
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
My Blog: www.voiceofthedba.com
guns_14
guns_14
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
Greg Charles
Greg Charles
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5037 Visits: 5895
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
guns_14
guns_14
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: Administrators
Points: 52138 Visits: 19005
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
My Blog: www.voiceofthedba.com
hitechimp
hitechimp
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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.
Dev
Dev
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3256 Visits: 1602
hitechimp (12/2/2011)
...


Please reply here for hitechimp's question.
http://www.sqlservercentral.com/Forums/Topic1215777-1550-1.aspx
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