locked
best approach for a security on a central DB system RRS feed

  • Question

  • so we have a lot of little DB's around the company and I've been asked to make a single DB server and move all the DBs there, which is fine, I can do that, where my knowledge is lacking is the security aspect.

    im trying to research security and schemas now, and im a little lost, im sure I'll figure it out, but I thought I'd ask, how would you handle the security config for a DB system like this? for the most part, assume there is two user accounts (read and read/write) and a DB owner (typically a dev, a lot of the apps are made in house) and then there is the DBA group. a Dev will be the owner of multiple DBs but the user accounts will be one to one.

    Thanks
    Justin

    Thursday, March 10, 2011 2:32 PM

Answers

  • Do you install SSMS on every user workstation? If so take a look at below.However, this works perfectly if the user is the owner of the database.

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbteste”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see
    master, tempdb, dbteste
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases

    Type in google "security sql server 2005" and first link is doc. SQL Server 2005 Security Best Practices - Microsoft Corporation


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, March 10, 2011 2:44 PM

All replies

  • Do you install SSMS on every user workstation? If so take a look at below.However, this works perfectly if the user is the owner of the database.

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbteste”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see
    master, tempdb, dbteste
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases

    Type in google "security sql server 2005" and first link is doc. SQL Server 2005 Security Best Practices - Microsoft Corporation


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, March 10, 2011 2:44 PM
  • Do you install SSMS on every user workstation? If so take a look at below.However, this works perfectly if the user is the owner of the database.

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbteste”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see
    master, tempdb, dbteste
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases

    Type in google "security sql server 2005" and first link is doc. SQL Server 2005 Security Best Practices - Microsoft Corporation


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, March 10, 2011 2:44 PM
  • Thanks I'll try that.

    that link didn’t work but I was able to find the document.

    do you (or does anyone) have a link that gives a good run down of login vs username and how schemas fit in there between DB's and objects?

    im reading the BOL and SQL internals and im having a hard time putting it all together.

    also I forgot to mention before, its sql 08

    Thanks

    Thursday, March 10, 2011 4:06 PM