December 31, 2008 at 6:37 am
Please come up with a minimum set of permissions that would allow the privileges listed below. We need to figure out what are the minimum privileges for this account, so it can be setup correctly.
1. Create databases, and all database objects
2. Execute procedures, functions
3. Be able to execute xp_cmd
4. Be able to bcp
December 31, 2008 at 8:43 am
What exactly is the requirement?
January 2, 2009 at 9:49 am
Is this for a specific user account or for an app account??
If you don't want to give the syadmin role, then these are some of the options
CREATE DATABASE permission defaults to members of the sysadmin and dbcreator fixed server roles only
xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.
I think BULKADMIN role will get you with doing the BCP tasks..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 2, 2009 at 11:45 am
thanks for the reply...
the requirement is ,... instead of giving a sysadmin role. I need to chose few permissions to satisfy all the permissions.
using
db_creator
bulk admin and
xp_cndshell proxy account
part of it was satisfied
to allow a user to create any object in the database, what server role or database role would be the best one, when it comes to minimum permissions ?
January 2, 2009 at 11:45 am
I was unaware of this xp_cmdshell proxy account .. thanks alot for the reply
January 2, 2009 at 12:00 pm
reddi_arjun (1/2/2009)
I was unaware of this xp_cmdshell proxy account .. thanks alot for the reply
The problem is that it gives everyone else access to xp_CmdShell also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 12:45 pm
how to give access of xp_cmdshell at user level.
i mean, if i need to give permission to only one user who is not sysadmin to access xp_cmdshell, how do we do that ?
January 2, 2009 at 12:56 pm
I am not sure if there is any way to do that directly.
Indirectly, you could try to implement an approach using SQL Agent Jobs or using Service Broker, but that would involve developing some custom facilities.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 1:06 pm
You could potentially mess with permissions on the XP in master and limit it to a specific role.
I wouldn't necessarily recommend this and be sure you have this well documented for DR or movement to another server, but it might work.
January 2, 2009 at 1:17 pm
reddi..to create any object you would need to use the dbo ( database owner) role, but how do you intend to keep track of all the objects that this user or app creates...if you are a DBA then please make sure all the concerns with regards to the integrity of data are addressed first and then take these steps...I hope the Guru's here have exact answer on how to achieve this trivial task..;)
Just Don't forget to backup the database...just in case you need it 🙂
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 2, 2009 at 1:18 pm
Hmm, I had thought that that was not possible, Steve, but looking at it now on my server, it appears that you can do that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 1:21 pm
Well Barry..it seems you just learnt a new thing in this New Year....:P
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 2, 2009 at 4:59 pm
Hey, I taught Mr. Young something. Didn't think that was likely to happen. Usually it's the other way around.
I don't recommend it, messing with master, IMHO, incurs risk that you'll seriously break something when you get into a DR place. Or a hardware/software upgrade.
January 2, 2009 at 5:54 pm
Heh. Happens almost every day, actually. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 6:53 am
Steve ,.. will you please tell me the process to change it in Master. I may not change it but would like to know the way we do it.
thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply