Setting Database password and permission

  • 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 🙂

  • 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.

  • 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

  • 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

  • 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?

  • 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.

  • 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.

  • hitechimp (12/2/2011)


    ...

    Please reply here for hitechimp's question.

    http://www.sqlservercentral.com/Forums/Topic1215777-1550-1.aspx

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply