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