January 20, 2017 at 12:20 am
UserA is Admin he has full rights in the server
UserB is marked with Public and sysadmin in server role tab and in User Mapping tab db_Owner
UserC is marked with Public in server role tab and in User Mapping tab db_Owner
UserD is marked with Public in server role tab and in User Mapping tab db_denydataread, db_denydatawrite.
userC and userD should not access my other 2 databases , so removed sysadmin role
UserB,UserC & UserD wants to access excel so when running below query
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sp_configure 'Ad hoc Distributed queries',1;
RECONFIGURE
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
users C and D gets error
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure sp_MSset_oledb_prop, Line 18
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 96
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure sp_MSset_oledb_prop, Line 20
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Procedure sp_MSset_oledb_prop, Line 21
You do not have permission to run the RECONFIGURE statement.
how to restict userC and userD accessing my other 2 databases.
when i give sysadmin in server role to userC and UserD they were able to access my database, i dont want userC and UserD to access my other 2 databases.
January 20, 2017 at 12:40 am
Those are server-level settings so the sp_configure must be run by a member of the sysadmin role, also RECONFIGURE WITH OVERRIDE is needed for updating the Running Configuration Value.
😎
Guess you meant How to restrict user's access 😉
January 20, 2017 at 12:56 am
Eirikur Eiriksson - Friday, January 20, 2017 12:40 AMThose are server-level settings so the sp_configure must be run by a member of the sysadmin role, also RECONFIGURE WITH OVERRIDE is needed for updating the Running Configuration Value.
😎Guess you meant How to restrict user's access 😉
I have done with Override as well, but no luck
Yes i meant to restrict user lever access only , but which option i need to opt
January 20, 2017 at 7:12 am
Without doing something crazy security wise, you'd have to leave those settings on all the time for users C and D to work. Out of curiosity, why are they trying to access an Excel spreadsheet from SQL Server? Wouldn't it be better to pull the data into a staging database on a periodic basis?
K. Brian Kelley
@kbriankelley
January 22, 2017 at 9:44 pm
K. Brian Kelley - Friday, January 20, 2017 7:12 AMWithout doing something crazy security wise, you'd have to leave those settings on all the time for users C and D to work. Out of curiosity, why are they trying to access an Excel spreadsheet from SQL Server? Wouldn't it be better to pull the data into a staging database on a periodic basis?
No it can't be as this server is used by third party for a long time and we have our own database in that server.
What are the security level we need to do to restrict the user C and D not to access our database.
January 23, 2017 at 2:31 am
No, it is not.
https://msdn.microsoft.com/en-us/library/ms176069(v=sql.110).aspx
Disables the configuration value checking (for values that are not valid or for nonrecommended values) for the recovery interval advanced configuration option.
Any configuration option can be reconfigured by using the WITH OVERRIDE option. In addition, RECONFIGURE WITH OVERRIDE forces the reconfiguration with the specified value.
If the setting is one that does not require a restart, the RECONFIGURE will change the setting immediately (ie, change the running value). If the setting is one that requires a restart, it will not. All WITH OVERRIDE does is disable some checks of sensible values.
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
January 23, 2017 at 2:40 am
Eirikur Eiriksson - Friday, January 20, 2017 12:40 AMRECONFIGURE WITH OVERRIDE is needed for updating the Running Configuration Value.
No, it is not.
https://msdn.microsoft.com/en-us/library/ms176069(v=sql.110).aspx
Disables the configuration value checking (for values that are not valid or for nonrecommended values) for the recovery interval advanced configuration option.
Any configuration option can be reconfigured by using the WITH OVERRIDE option. In addition, RECONFIGURE WITH OVERRIDE forces the reconfiguration with the specified value.
If the setting is one that does not require a restart, the RECONFIGURE will change the setting immediately (ie, change the running value). If the setting is one that requires a restart, it will not. All WITH OVERRIDE does is disable some checks of sensible values.
Thank you Gail for the correction!
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply