SQL Server Security: Fixed Roles

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityfixedroles.asp

    K. Brian Kelley

  • Great article!!

    I was wondering, what security setup do you put in place for your development environments? I have been trying to set up a development environment without giving the developers sysadmin rights, but most of our developers create DTS packages which make it hard to share development. I do not want to use SQL logins to get around this.


    Dean Christie

    Edited by - dmc-co on 11/04/2003 12:35:31 PM

  • That is indeed a good article, in future looking forward to read some more on same topic

    Kindest Regards,
    Jaiprakash M Bankolli
    My Blog
    Suggestions for me

  • why did you republished 2003 article?

  • We republish popular articles periodically. It gives new people to the site a chance to catch them.

  • Yah.

    I set an sp as a startup, created a login Hacker with access to Master as db_datawriter, db_datareader and db_ddladmin. Connected as Hacker user in Management Studio I was able to modify the stored procedure to add a line for adding this Hacker to Sysadmin role. I did re-check that the Hacker person did not have ANY server roles.

    I was able to restart the SQL Server from Management Studio connected to SQL Server as Hacker. After I restarted the service the Hacker person was a sysadmin. While I can find the explanation that I was able to restart the service (Management Studio is run under the logged in user process that is a Windows login and my Windows login has admin rights) I find the whole thing sort of ... you know. I will re-test it Monday just to make sure. My SQL Server is 2005 RTM. I will re-test on SP 1 and SP2.


    Regards,Yelena Varsha

  • It would be nice to put links in this old article to articles you published (later)which deal with SS 2005. And links to articles about fixed database roles and server logins  - because all these go in a package ... Or I'm wrong?

  • I actually just ran into a "problem" involving the server roles in SQL Server 2000 (and I believe 2005). We have a VB application used in house, and users have a SQL Server login. Logging in the application uses the user_name() function. Some of our users also belong to server roles. We've found that for those users, user_name() returns "dbo" instead of their user name. Instead, we apparently need to use something like system_user to return their actual user name. This seems stupid really, but apparently is a known issue? It was news to us, and now we need to change a good number of our stored procedures. Bah!

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

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