SQL - Account

I created a user that execute a stored procedure.  This stored procedure than goes into each database to see if a specific table name exists and list only databases with that table name.  

This is the error I get when I log in as that user to execute the stored procedure.


The server principal "svc_sysadmin" is not able to access the database "model" under the current security context.

What kind of permission do I need to grant so that account?  The stored procedure also has an Alter login in there as well to change password.
holemaniaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
you need to grant access to ALL dbs included system DBs (model DB too)  or just exclude System DBs from your search
Aneesh RetnakaranDatabase AdministratorCommented:
I would alter the sql procedure logic to exclude all system dbs.
Michael DyerSenior Systems Support AnalystCommented:
If you post your stored procedure, we can give you specific help.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, the SP's code will help us helping you.
holemaniaAuthor Commented:
So here's my stored procedure.  Basically look at each database to see if there's a table called "USER_PERMISSION".  If that table exists in that database, count the record and return.  Final select statement query only retrieve those databases.  We have a lot of these databases mix with other databases for different site within the same sql instance.  So I created this SP to find those databases.  I went and create a sql account to run this SP with limited access, but getting the error I posted.

CREATE PROCEDURE SR_FIND_DB

AS

DECLARE @dbname VARCHAR(30), @sql VARCHAR(MAX)

DECLARE @temp TABLE(
		DB VARCHAR(50),
		ROW_COUNT INT
		);
		
DECLARE db_cursor CURSOR FOR
SELECT	NAME FROM SYS.sysdatabases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbname

WHILE(@@FETCH_STATUS=0)
	BEGIN

	SET @sql = 
	'
	SELECT '''+@dbname+''' DB,  COUNT(*) FROM [' + @dbname + '].SYS.TABLES WHERE [NAME] = ''USER_PERMISSION''
	'
	
	INSERT INTO @temp
	EXECUTE(@sql)

	FETCH NEXT FROM db_cursor INTO @dbname

	END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT	*
FROM	@TEMP
WHERE	ROW_COUNT = 1

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to grant select permission on that system table in all databases:
GRANT SELECT ON sys.tables TO UserName

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
I just remember that if the user doesn't have permissions on the table 'USER_PERMISSION' then it won't find it.
So it's better to grant the view any definition instead of the grant select as I mentioned above:
GRANT VIEW ANY DEFINITION TO LoginName

Open in new window

holemaniaAuthor Commented:
Yeah that's the issue is I don't know which database would have that table, and there's always new database added.  So the SP is supposed to find those databases.

Is there a server role, that may not need to be sysadmin that can have similar access without having to grant permission to each database, table, etc?
Michael DyerSenior Systems Support AnalystCommented:
Try giving your user the db_datareader role.  
 
Members of the db_datareader fixed database role can read all data from all user tables.
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, I don't think you want to give permission to read all tables in all databases.
This command GRANT VIEW ANY DEFINITION TO LoginName allows the user to see the structure of the tables without accessing the data. And it's valid for all databases.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
holemaniaAuthor Commented:
Michael,

I think that would work, but the issue is that the SP supposed to be dynamic, and kind of defeat the purpose of having the SP.  If I add the db_datareader, I would have to add that to the account every time a new database is added to the sql instance.  That's what I want to avoid having to do and have the SP be dynamic where anytime a database is added, it's able to query and see if it's got that table in there or not.

Works fine as sysadmin role at the server level.  However, don't want to grant that kind of power to this service account.
Vitor MontalvãoMSSQL Senior EngineerCommented:
However, don't want to grant that kind of power to this service account.
Did you tried to give the permissions I told you?
Anthony PerkinsCommented:
If all you want is a count of rows, then why not use the system tables (sys,tables, sys.indexes and sys.partitions) and bypass the whole GRANT SELECT on table to user problem.  It would also be 10 times faster...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.