How can I give a non sa account sysadmin like permissions without giving it the role

Question

Post reply

How can I give a non sa account sysadmin like permissions without giving it the role

tannguyen00

Old Hand

Points: 343
More actions
May 13, 2008 at 4:31 pm

#190518

Hi All,
I've created a non sa user in order to install an app but cannot go through the install process because it cannot run the following execute:
exec master..xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'
I get an error telling me that Permission is denided and that the user must be a member of the 'sysadmin' server role.
the app runs the command to making sure that sqlserveragent is running.
Is there any way around this? thanks for the help.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Add the user to master and grant EXECUTE on xp_servicecontrol. It won't have any other sysadmin permissions.

Greg

tannguyen00 Old Hand Points: 343 More actions · Answer 2

Hi Greg...

Thanks for the quick reply but it did not resolve the issue.

I failed to mention that this is sql 2005. Belos is what I did based on your reply.

1. When into MSSMS as sa. Create a sql user called nonsa

2. mapped it to master, msdb, model

3. when into the extended stored procedure and granted execute permission to nonsa

4. closed Microsoft SQL Server Management Studio

5. Logged back into Microsoft SQL Server Management Studio

6. try to execute the following script. exec master..xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

7. Receive the following error:

Msg 22003, Level 16, State 1, Line 0

Error executing 'xp_servicecontrol': Permission denied. User must be a member of 'sysadmin' server role.

Any other ideas? Also I noticed that when I logged in as the nonsa user, i didn't see the sql server agent.

thanks again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You could add the login to the sysadmin fixed server role. It gives the account full systed admin privilidges, but if you're OK with that...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

If u add the user to a sysadminm role then remove it after your purpose is over.

"Keep Trying"

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 5

Chirag (5/14/2008)
Hi
If u add the user to a sysadminm role then remove it after your purpose is over.

True but then why are you creating a SQL Login, just for installation, at all?

I would advocate you use DBA have staff who already administrative access to carry out installation.

tannguyen00 Old Hand Points: 343 More actions · Answer 6

Hi All,

Thanks for all the replies. Unfortunately the people that I'm working with will not add the nonsa user to the sysadmin role to the test enviornment or production. Since i'm doing a lot of install and uninstall in the test enviornment, I don't always have access to their DBAs.

Adding the sysadmin role to the nonsa user is quick and simple and I wished that I was allowed to do that but I can't. Looks like i'm SOL unless there's any other workarounds....

By the way, i'm a newbie on this and am really impress by all the responses and the level of knowledge. Thank you all!!!! Much appreciated!

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 7

tannguyen00 (5/14/2008)
Hi All,
Thanks for all the replies. Unfortunately the people that I'm working with will not add the nonsa user to the sysadmin role to the test enviornment or production. Since i'm doing a lot of install and uninstall in the test enviornment, I don't always have access to their DBAs.
Adding the sysadmin role to the nonsa user is quick and simple and I wished that I was allowed to do that but I can't. Looks like i'm SOL unless there's any other workarounds....
By the way, i'm a newbie on this and am really impress by all the responses and the level of knowledge. Thank you all!!!! Much appreciated!

The reason they aren't giving you this access is to prevent precisely what you are trying to do.

You need to be doing this stuff in a development environment, not on production or pre-production. Once you finished playing about with it you can pass it on to for deployment by a team with the access to do so. Building in a workaround like "nonsa" is a really bad idea.