asked on

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.

EugeneZ

you need to grant access to ALL dbs included system DBs (model DB too) or just exclude System DBs from your search

Aneesh

I would alter the sql procedure logic to exclude all system dbs.

Michael Dyer

If you post your stored procedure, we can give you specific help.

Vitor Montalvão

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

holemania

ASKER

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ão

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ão

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

holemania

ASKER

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 Dyer

Try giving your user the db_datareader role.

Members of the db_datareader fixed database role can read all data from all user tables.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

holemania

ASKER

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ão

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 Perkins

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