Wierd situation. sa [SysAdmin] cannot grant SysAdmin rights to other logins.

Question

Wierd situation. sa [SysAdmin] cannot grant SysAdmin rights to other logins.

matthew.mark.ctr

Old Hand

Points: 379
More actions
August 9, 2010 at 4:37 pm

#227622

I'm not a newbie but feel like one today. I am taking over a few sql server instances for someone who left.
1. I logged in as sa.
2. Attempted to give myself, a windows login that is also an administrator on the server, Sysadmin rights.
3. Everytime I try, I get the Access Denied error 10011.
Attempting to keep this simple, are there any situations where sa, having SysAdmin server role, cannot grant rights to others??????
I also made sure I was not created as a user in any of the databases. I'm sure most of you who are reading this, "know what I am talking about."
Any help at all. thanks.
mm:-)

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i once worked in a shop where the original sa login was renamed and a new cripped sa login was created as a honey trap for developers who shouldn't be using ther sa login on production;

could you check your permissions with this and see if you reall have sysadmin priviledges?

SELECT

name AS Login,

sysadmin =

CASE

WHEN sysadmin = 1 THEN 'X'

ELSE ''

END,

securityadmin =

CASE

WHEN securityadmin = 1 THEN 'X'

ELSE ''

END,

serveradmin =

CASE

WHEN serveradmin = 1 THEN 'X'

ELSE ''

END,

setupadmin =

CASE

WHEN setupadmin = 1 THEN 'X'

ELSE ''

END,

processadmin =

CASE

WHEN processadmin = 1 THEN 'X'

ELSE ''

END,

diskadmin =

CASE

WHEN diskadmin = 1 THEN 'X'

ELSE ''

END,

dbcreator =

CASE

WHEN dbcreator = 1 THEN 'X'

ELSE ''

END,

bulkadmin =

CASE

WHEN bulkadmin = 1 THEN 'X'

ELSE ''

END,

CONVERT(CHAR(16),createdate,120) AS 'DateCreated'

FROM master.dbo.syslogins

WHERE

sysadmin = 1

ORDER BY NAME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

matthew.mark.ctr Old Hand Points: 379 More actions · Answer 2

Thanks for your time, I appreciate it.

I solved the problem, see how, at the bottom.

I did run the script (above) and the records returned showed permissions listed as sysadmin. (I trimmed the names)

I didn't mention that

1. I am using SQL Server 2005 and

2. As sa I cannot grant server roles to anyone.

So, here's what I did to solve it.

I logged into the SQL instance on my local workstation and as expected I can grant sysadmin privileges as sa.

I looked at what was different. My machine installation uses my domain name and password for login.

The problem server used "local system account." I've read some best practices documents and did not notice this as a restriction. Instead, it mentioned it might be "too lose" with security.

So I changed the login for the Main SQL Server Service to my domain name and password.

Now when I log in as sa, I can grant SysAdmin rights.

I still don't know why, though.

thanks:-P

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Just a note...you should never have SQL running under a user's domain account (such as yours). If your account is locked out, or your password changes, or someone messes up and sets your account to inactive, SQL will stop working. It should be run under a dedicated service account that has a password set to never expire.

edit: And if you're running Server 2008, you can apply granular password policies to OUs in AD..it would be best to have this account with no lockout policy either, so if someone figures out which account it runs under, they can't DOS it by just trying 10 bad passwords and locking it out.

matthew.mark.ctr Old Hand Points: 379 More actions · Answer 4

Thank you for the heads up and And I agree that service accounts are optimum.

I should have mentioned that I was talking 'development', not 'production'.

I do have a username for admin situations. We have a group of developers who I grant the group sysAdmin rights too.

And, I will be asking for another service account in a bit.

We log in with smart cards, not names and passwords. I put in a SQL Server Central and Google search about 6 months ago for anyone who knew how to set up service accounts/ SQL Server Security when only smart cards are used to log into the domain. No one even replied that they even knew what I was talking about. So, once again, we are left to our own devices to solve the problems.

I have found that so often people post only the optimum solutions when solving a problem. I tried to point out one of the "gotchas" that can take some time resolving, without guidance. Sometimes we need to work in a less than optimum situation. This is where the little do's and don'ts come especially handy (best practices etc).

None of the error messages I got led me to the cause of the problem. This eventually told me the error message documentation writers, as usual, were probably making a lot of assumptions when writing explanations. Likewise, so often also, posters do not explain "why something is happening." They are like the new breed of auto mechanic who can only "change out parts" but doesn't know why the problem occured.

thanks,

mm