SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Security: Fixed Roles


SQL Server Security: Fixed Roles

Author
Message
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25564 Visits: 1917
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityfixedroles.asp

K. Brian Kelley
@‌kbriankelley
rundino
rundino
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 10
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
Jaiprakash M Bankolli
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 13
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
EugeneZ-162636
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 562
why did you republished 2003 article?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150228 Visits: 19450
We republish popular articles periodically. It gives new people to the site a chance to catch them.

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
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9160 Visits: 601

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 Varshal

Iordan Slavov
Iordan Slavov
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 44
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
Pete T-366679
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 248
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!
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