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 (10K reputation)

Group: Moderators
Points: 10148 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 (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

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

Group: Administrators
Points: 61503 Visits: 19097
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4552 Visits: 595

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 Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 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
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 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