We help IT Professionals succeed at work.

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.
Comment
Watch Question

Commented:
you need to grant access to ALL dbs included system DBs (model DB too)  or just exclude System DBs from your search
AneeshDatabase Consultant
Top Expert 2009

Commented:
I would alter the sql procedure logic to exclude all system dbs.
Michael DyerSenior Systems Support Analyst

Commented:
If you post your stored procedure, we can give you specific help.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, the SP's code will help us helping you.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
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

Author

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 Analyst

Commented:
Try giving your user the db_datareader role.  
 
Members of the db_datareader fixed database role can read all data from all user tables.
IT Engineer
Distinguished Expert 2017
Commented:
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.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
However, don't want to grant that kind of power to this service account.
Did you tried to give the permissions I told you?
Top Expert 2012

Commented:
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...