Permissions issue with a sysadmin account

Question

Permissions issue with a sysadmin account

BrainDonor

SSCoach

Points: 19254
More actions
January 19, 2017 at 4:08 am

#334296

One of the devs I work with is getting the following error when trying to examine a stored proc:

I know that this can be as a result of insufficient permissions, which is odd for a sysadmin account. So using fn_my_permissions, on a server he has no issues with:
CONNECT SQL
SHUTDOWN
CREATE ENDPOINT
CREATE ANY DATABASE
CREATE AVAILABILITY GROUP
ALTER ANY LOGIN
ALTER ANY CREDENTIAL
ALTER ANY ENDPOINT
ALTER ANY LINKED SERVER
ALTER ANY CONNECTION
ALTER ANY DATABASE
ALTER RESOURCES
ALTER SETTINGS
ALTER TRACE
ALTER ANY AVAILABILITY GROUP
ADMINISTER BULK OPERATIONS
AUTHENTICATE SERVER
EXTERNAL ACCESS ASSEMBLY
VIEW ANY DATABASE
VIEW ANY DEFINITION
VIEW SERVER STATE
CREATE DDL EVENT NOTIFICATION
CREATE TRACE EVENT NOTIFICATION
ALTER ANY EVENT NOTIFICATION
ALTER SERVER STATE
UNSAFE ASSEMBLY
ALTER ANY SERVER AUDIT
CREATE SERVER ROLE
ALTER ANY SERVER ROLE
ALTER ANY EVENT SESSION
CONTROL SERVER
And on the server he is having issues with:
CONNECT SQL
VIEW ANY DATABASE
So I deleted the account from that server and recreated it (from AD) with sysadmin server role and it hasn't changed.
Any idea how this might have happened and how to remedy it? I know I could add the permissions individually but I'm more interested in knowing what caused this.
Steve Hall
Linkedin
Blog Site

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

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

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

sysadmin has all permissions and cannot be denied anything (it bypasses all permission checks). So if he's logging in with that sysadmin account, it can't be a permission problem.

Check the server he's logging into is the one he thinks he's logging into, and that the account he's using is the one with sysadmin permissions.
Check the version of SSMS and the version of SQL, make sure he's not using an old version of SSMS

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

Thom A SSC Guru Points: 99130 More actions · Answer 2

This looks like the error that existed with a prior version of SSMS 2016 (I believe it was either the October or November version). What version of SSMS are they using?

If I recall, using CREATE TO instead of ALTER TO/modify was the work around, but the fix is update SSMS 2016.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

Doesn't the last bit of the error message give it away? "The text is encrypted"

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

Ian Scarlett - Thursday, January 19, 2017 4:21 AM
Doesn't the last bit of the error message give it away? "The text is encrypted"

Didn't notice that. Yup, if the procedure's encrypted it can't be scripted out.

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

BrainDonor SSCoach Points: 19254 More actions · Answer 5

GilaMonster - Thursday, January 19, 2017 4:16 AM
sysadmin has all permissions and cannot be denied anything (it bypasses all permission checks). So if he's logging in with that sysadmin account, it can't be a permission problem.
Check the server he's logging into is the one he thinks he's logging into, and that the account he's using is the one with sysadmin permissions.
Check the version of SSMS and the version of SQL, make sure he's not using an old version of SSMS

Thank you Gail - he'd switched logins to test security for a login with more granular permissions and forgotten to switch back whenever the login screen on SSMS popped up. I didn't notice the login name on the bottom of the screen - my apologies and thanks for your time.

Steve Hall
Linkedin
Blog Site

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

Hopefully the developers are checking their DDL scripts into a version control system like Git, SVN, or TFS.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho