Help Needed : permission denied on spt_values
-
Hi All,
Strangely I started getting the following error on the Production Server since this morning for some standard users <b>who are not in master public.</b>
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Server: Msg 229 Level 14, State 5
[Microsoft][ODBC SQL Server Driver][SQL Server] SELECT permission denied on object 'spt_values', database 'master', owner'dbo'
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Few days back, I have created some logins, then added them as users into one of the user database. But this morning I started getting calls for the above issue.
I am not sure if I removed these users from the master yesterday. Yesterday many changes went in. I did not remember if this had been changed.
Can any one help!!!! Please.....
.
-
Have you removed 'guest' user from master database or changed 'guest' user's permissions?
-
Yes. I did add guest to "db_denydatareader" role yesterday.
Whats the relation?! Is that how I messed it up?!?!??!?.
-
The guest user allows a login without a user account in database to access that database. The login obtains the database permissions granted to this 'guest' account.
-
Thanks a lot for your help and info Allen.
Would it be a good idea to add guest to db_denydatawriter role in master database?! I wanted to feel safe with that account being there.
.
-
quote:
Would it be a good idea to add guest to db_denydatawriter role in master database?! I wanted to feel safe with that account being there.
Guest does not have permissions to write to any tables or views. Every user that isn't explicitly mapped to a user in master (which would be everybody by default... and there's not usually a reason to add a login as a user to master) comes in as guest. Guest is required in master and tempdb.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
The 'guest' user doesn't have any database permissions in master database. Like any other users, it is a memeber of public role so It has all permissions the public role has. The 'guest' user can be deleted and added to all databases except master and tempdb where it must always exist.
You can grant all logins to access master and tempdb database and those logins will have all permissions as 'guest' user because all logins must be member of public role. The public role does not have 'insert/update/delete' permissions to tables in master database and it is not necessary to add 'guest' user to db_denydatawriter role. You need tie the permissions of public role if you don't feel comfortable with security in master database.
-
quote:
You need tie the permissions of public role if you don't feel comfortable with security in master database.
I'll come back with an "it depends" here.
If he had explicit users in the database, he wouldn't want to tie permissions to public but to guest. That would allow those explicit users access while preventing logins who don't have mappings.
With that said, I don't know of very many good reasons to have explicit user mappings into master.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
quote:
With that said, I don't know of very many good reasons to have explicit user mappings into master.
Having explicit user in master, He will have option to tie the permissions to each individual users according to their needs.
-
Right, but everyone belongs to the public role. If he denies to public (as opposed to just revoke), the deny will apply to everyone who isn't a sysadmin.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Thank You Brian and Allen for sharing the thoughts...
Thats exactly what I have in mind. But I kindof unstable in this idea. I am thinking of creating a group with all the minimum permissions. This is basically I don't like the concept of public & don't trust it.
Or sometimes I kind of thinking that these users should not be there in the master at all, since I want to secure that fully.
I donno I am kind of lost sometimes when I think of security (tooo much of security....)
Edited by - mdamera on 07/16/2003 4:14:28 PM
.
-
quote:
Thats exactly what I have in mind. But I kindof unstable in this idea. I am thinking of creating a group with all the minimum permissions. This is basically I don't like the concept of public & don't trust it.Or sometimes I kind of thinking that these users should not be there in the master at all, since I want to secure that fully.
This is a very dangerous road to go down. Should you alter permissions in master, your SQL Server becomes an unsupported configuration so far as Microsoft is concerned. If you have to call in support and they determine you've changed permissions, they'll force you to roll back your changes before proceeding any further.
Also, you need to be aware of what will break stuff and what won't. There are some real surprises, so before you roll anything into production, test it in a non-production environment. And before you make the change, regardless of environment, take a full backup of master.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Whats the relation?! Is that how I messed it up?!?!??!?Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply