login server role take

Question

I have a windows domain account, for example, let's call it domain\myuser who is in the administrator group in the windows server that SQL server installed in.

And I can see from sql management studio-security-login, I can see teh builtin\administrators and also domain\myuser listed there. 
 since the account is built-in administrator group, so it has sysadmin.
but I found the user also listed as a separate login that  is setup server role as dbcreator and securityadmin.

Since this was setup by some other person no longer here, I'm not sure why is like this. I will not change it.
But my question is: if the account is setup separatedly, will this account take precedence over its role as the setup role for all builtin\administrators ?
Is this account no longer a sysadmin role?

Thanks

Answer 1

As long as you are not revoking sysadmin from the default windows login BUILTIN ADMIN , all the windows administrators will be a sql server admin (under sysadmin role) .

Dbcreator (create , alter and restore db)and securityadmin(create login , credential , schemas and users ) are server roles which has certain level of privileges (But sysadmin role can do everything).
In your case , Irrelavant of they just having security admin and db creator they are still under sysadmin role at the sql server because of builtin admin.

---

Thanks, Leks

Answer 2

Yes, I thought the same as you wrote.

But while I do a restore process from sharepoint central admin, it isn't succesful unless I particular checked the sysadmin role for the account.
I thought it should already have sysamin role in the default builtin\administrator group.

But apparently if I add the sysadmin for the account, the retore job will be successful, but if i unchecked for this accout, it will fail.

Answer 3

Hi Anna,

 

Have you checked the login privilege locally , whether this login has privileges of sysadmin role ?

Is this behaviour only from SharePoint portal ?

---

Thanks, Leks

Answer 4

How can I check that other than the sysadmin is checked for builtin\administrators, and the user is in the admin group?

Thanks

Answer 5

What i meant here is ,
Just login to sql server with your domain\username and see if he is able to run this ,
sp_configure 'show advanced options',0
go
reconfigure
go

-- if this succeeds the login has sysadmin role and if not it just has dbcreator and securityadmin role

To see whether the builtin admin is still under sysadmin role run this ,

select loginname , sysadmin from sys.syslogins where sysadmin = 1 and loginname like 'BUILTIN\Administrators'

---

Thanks, Leks

Answer 6

Hello,

I run the spconfigure, and I got

Configuration option 'show advanced options' changed from 0 to 0. Run the RECONFIGURE statement to install.

Then I run:select loginname , sysadmin from sys.syslogins where sysadmin = 1 and loginname like 'BUILTIN\Administrators'

I got
login name
 BUILTIN\Administrators  1

So from this result I guess, this account DOES have sysadmin role, correct?

But why after I checked sysadmin for this account, then I can restore site from sharepoint portal, if I unchecked, it will fail and say
Cannot open database "WSS_Content_Test_80" requested by the login. The login failed.
Login failed for user domain\myuser.


That's wierd.

 

 

 

Answer 7

Yes as we tried , we now are knowing that the domain\user is still sysadmin role.
But can you check what is the default database for domain\user in your sql server . If it has WSS_Content_Test_80 , then can you change it in to master and then try your restore.

---

Thanks, Leks

Answer 8

It is actually setup to default to master.

I did another run from sharepoint restore. this time I didn't put username and password in, and it will automatically use the timer service account.
This time it works. The error is kind of misleading.


Thanks very much for me to figure out sql part.