SQL Server Security: Fixed Roles

Question

SQL Server Security: Fixed Roles

K. Brian Kelley

SSC Guru

Points: 114642
More actions
October 18, 2003 at 12:00 am

#82807

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityfixedroles.asp
K. Brian Kelley
@kbriankelley

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

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

rundino SSC Enthusiast Points: 173 More actions · Answer 1

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.

Thanks

Dean Christie

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

Jaiprakash M Bankolli SSC Enthusiast Points: 125 More actions · Answer 2

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

EugeneZ-162636 Default port Points: 1448 More actions · Answer 3

EugeneZ-162636

Default port

Points: 1448

May 4, 2007 at 4:44 am

#704306

why did you republished 2003 article?

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 4

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

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

Iordan Slavov Say Hey Kid Points: 666 More actions · Answer 6

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?

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 7

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!