July 4, 2018 at 2:32 am
I've been trying to work out for hours how I have permission to use 'dbo'.
- I'm logged onto the server instance using my AD credentials.
- I've run SELECT SUSER_ID () which returns 2 which on cross reference to sys.database_principals is dbo.
- To my understanding, this will be caused by either membership of sysadmin role, sa login or ownership of the database.
- The database is owned by NT AUTHORITY\NETWORK SERVICE -
- Looking at sys.serverpermission the only entries are for CONNECT, CONNECT SQL and VIEW %
- Looking at sys.server_principals, sysadmin has been assigned to the sa account, certificate mapped logins and NT SERVICE or NT AUTHORITY principals. No other fixed or non-fixed roles are assigned.
I've also tested and I have permission to create databases.
Any ideas?
July 4, 2018 at 3:00 am
permissions (security principles) are given at 3 levels, Windows server / AD, SQL server and DB
is you AD account an administrator
***The first step is always the hardest *******
July 4, 2018 at 3:03 am
No it's not
July 4, 2018 at 3:15 am
run the following to review your server permissions:SELECT pr.principal_id, pr.name,
pr.type_desc, pe.state_desc,
pe.permission_name
FROM sys.server_principals AS pr JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
You could also be a member of a security group that has elevated permissions.
Apart from this, I am struggling to read the real question here, maybe elaborate a bit.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 4, 2018 at 3:25 am
1 sa SQL_LOGIN GRANT CONNECT SQL
2 public SERVER_ROLE GRANT VIEW ANY DATABASE
101 ##MS_SQLResourceSigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT VIEW ANY DEFINITION
102 ##MS_SQLReplicationSigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT AUTHENTICATE SERVER
102 ##MS_SQLReplicationSigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT VIEW ANY DEFINITION
102 ##MS_SQLReplicationSigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT VIEW SERVER STATE
103 ##MS_SQLAuthenticatorCertificate## CERTIFICATE_MAPPED_LOGIN GRANT AUTHENTICATE SERVER
105 ##MS_PolicySigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT CONTROL SERVER
105 ##MS_PolicySigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT VIEW ANY DEFINITION
106 ##MS_SmoExtendedSigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT VIEW ANY DEFINITION
257 ##MS_PolicyTsqlExecutionLogin## SQL_LOGIN GRANT CONNECT SQL
257 ##MS_PolicyTsqlExecutionLogin## SQL_LOGIN GRANT VIEW ANY DEFINITION
257 ##MS_PolicyTsqlExecutionLogin## SQL_LOGIN GRANT VIEW SERVER STATE
259 [ADGROUP1] WINDOWS_GROUP GRANT CONNECT SQL
260 NT AUTHORITY\NETWORK SERVICE WINDOWS_LOGIN GRANT CONNECT SQL
261 BUILTIN\Administrators WINDOWS_GROUP GRANT CONNECT SQL
262 NT SERVICE\SQLWriter WINDOWS_LOGIN GRANT CONNECT SQL
263 NT SERVICE\Winmgmt WINDOWS_LOGIN GRANT CONNECT SQL
264 NT SERVICE\MSSQL$SERVER WINDOWS_LOGIN GRANT CONNECT SQL
265 NT AUTHORITY\SYSTEM WINDOWS_LOGIN GRANT CONNECT SQL
266 BUILTIN\Users WINDOWS_GROUP GRANT CONNECT SQL
269 ##MS_PolicyEventProcessingLogin## SQL_LOGIN GRANT CONNECT SQL
270 ##MS_AgentSigningCertificate## CERTIFICATE_MAPPED_LOGIN GRANT CONNECT SQL
2 public SERVER_ROLE GRANT CONNECT
2 public SERVER_ROLE GRANT CONNECT
2 public SERVER_ROLE GRANT CONNECT
2 public SERVER_ROLE GRANT CONNECT
My account is in the BUILTIN\Users Windows Group. This group has not been assigned any server roles.
July 4, 2018 at 3:30 am
To elaborate, I would like to understand how I have acquired permission to use the dbo database principal.
July 4, 2018 at 3:45 am
james.smith3 - Wednesday, July 4, 2018 2:32 AMI've been trying to work out for hours how I have permission to use 'dbo'.- I'm logged onto the server instance using my AD credentials.
- I've runSELECT SUSER_ID ()which returns 2 which on cross reference to sys.database_principals is dbo.- To my understanding, this will be caused by either membership of sysadmin role, sa login or ownership of the database.
- The database is owned by NT AUTHORITY\NETWORK SERVICE -
- Looking at sys.serverpermission the only entries are for CONNECT, CONNECT SQL and VIEW %
- Looking at sys.server_principals, sysadmin has been assigned to the sa account, certificate mapped logins and NT SERVICE or NT AUTHORITY principals. No other fixed or non-fixed roles are assigned.I've also tested and I have permission to create databases.
Any ideas?
grant yourself db_owner if you can.
Sorry for my bad English..
July 4, 2018 at 3:45 am
what roles is this group a member of?
My account is in the BUILTIN\Users Windows Group. This group has not been assigned any server roles.
Check the documentation on finding your permissions with built-in functions here
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 4, 2018 at 4:06 am
No roles and no permissions directly assigned to the server principal
July 4, 2018 at 6:03 am
james.smith3 - Wednesday, July 4, 2018 2:32 AMI've been trying to work out for hours how I have permission to use 'dbo'.- I'm logged onto the server instance using my AD credentials.
- I've runSELECT SUSER_ID ()which returns 2 which on cross reference to sys.database_principals is dbo.- To my understanding, this will be caused by either membership of sysadmin role, sa login or ownership of the database.
- The database is owned by NT AUTHORITY\NETWORK SERVICE -
- Looking at sys.serverpermission the only entries are for CONNECT, CONNECT SQL and VIEW %
- Looking at sys.server_principals, sysadmin has been assigned to the sa account, certificate mapped logins and NT SERVICE or NT AUTHORITY principals. No other fixed or non-fixed roles are assigned.I've also tested and I have permission to create databases.
Any ideas?
SUSER_ID returns the server principal id, not the database principal id. And dbo is not s server principal.
You can execute SELECT SUSER_NAME(2) to find the login name for SUSER_ID of 2. It should be public, not dbo
Sue
July 6, 2018 at 8:12 am
james.smith3 - Wednesday, July 4, 2018 2:32 AMI've been trying to work out for hours how I have permission to use 'dbo'.- I'm logged onto the server instance using my AD credentials.
- I've runSELECT SUSER_ID ()which returns 2 which on cross reference to sys.database_principals is dbo.- To my understanding, this will be caused by either membership of sysadmin role, sa login or ownership of the database.
- The database is owned by NT AUTHORITY\NETWORK SERVICE -
- Looking at sys.serverpermission the only entries are for CONNECT, CONNECT SQL and VIEW %
- Looking at sys.server_principals, sysadmin has been assigned to the sa account, certificate mapped logins and NT SERVICE or NT AUTHORITY principals. No other fixed or non-fixed roles are assigned.I've also tested and I have permission to create databases.
Any ideas?
Just a shot in the dark... use the xp_logininfo stored procedure to find all AD groups that you're a member of and then check those groups for the DBO privs.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy