Windows authentication windows user rights

Question

When we use windows authentication in sql server management studio. Does user account which used to get logged into, account type put impact on user authorization.

I use windows authentication to logged into sql management studio, But unable to change sa password and sql authentication mode to sql and windows. I am getting permission related error:

For authentication mode.

The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'.`

For sa password change

Cannot alter the login 'sa', because it does not exist or you do not have permission.

Below image shows the permission of windows user.

Below is permission

I ran sql management studio as administrator, but that also did not help. — Rudra, Dec 20 '16 at 07:38
Which user do you use when logging in? Can you share its permissions? (check both login and database user) — Alexei - check Codidact, Dec 20 '16 at 08:22
I have edited question to show permission, SqlServer is a fresh install and only have system database, Before creating any new database i want to change sa password and authentication mode to mix mode. — Rudra, Dec 20 '16 at 08:37
It would be more relevant to show the permissions of the user you have used to login it (your windows user). — Alexei - check Codidact, Dec 20 '16 at 08:54

score 0 · Answer 1 · edited May 23 '17 at 11:53

0

this type issue is GRANT Permission Issue

// grant the user directly access to the procedure

Grant Execute ON [sys].[xp_instance_regread] TO [DOMAIN\USER];

//to check and verify that  the user has the privilege granted to him

EXECUTE AS USER = ‘DOMAIN\USER’;Select * from fn_my_permissions(‘xp_instance_regread’,’Object’)

Go

OR

USE DatabaseName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

Reference

edited May 23 '17 at 11:53

Community

1
1

answered Dec 20 '16 at 08:21

Genish Parvadia

1,437
3
17
30

Getting following error.. "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. " – Rudra Dec 20 '16 at 08:31
If a login is member of the db_owner role (which is not the same as dbo, i.e. owner of the database) it can still be denied access to some objects. – Genish Parvadia Dec 20 '16 at 08:34
There are only system database.. When i ran SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); on master database it return empty result set. – Rudra Dec 20 '16 at 08:42

Rudra · Accepted Answer · 2016-12-20T11:22:09.353

0

Issue resolved by following steps..

1) Open configuration manager

2) On left side click on sql server service

3) on right side Select Sql instance (i.e. sql server(SQL EXPRESS))

4) Right Click -> Click on properties

5) Click on startup parameters tab

6) type -m and click add

7) restart service from configuration manager

8) Open management studio and logged into using windows authentication Now you are in admin role.

9) change the sa password and authentication mode.

10) Remove the -m from the startup parameters tab

11) Open management studio and logged into using sa login details.

edited Dec 20 '16 at 11:22

answered Dec 20 '16 at 11:16

Rudra

148
1
10

After step 2, there is nothing listed on the right-hand side. (Even though I do have SQL server running). – Steve Smith Mar 19 '20 at 11:49

Windows authentication windows user rights

2 Answers2