Granting SELECT permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Granting SELECT permissions

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-04-14 : 13:12:21

SQL7.

I want to be able to grant SELECT permissions to a User (everyone logs in using the same username) for all tables in a database by running a stored procedure.

Here is what i have done so far.....


CREATE PROCEDURE usp_GrantPermissions
/* Grant SELECT permissions to ALL tables in UserRpt DB */
AS

DECLARE @tablename varchar(50)

DECLARE @tablepermission CURSOR 

SET @tablepermission = CURSOR FOR

SELECT
	name
FROM 
	sysobjects
WHERE 
	type = 'U'

OPEN @tablepermission

FETCH NEXT FROM @tablepermission
INTO @tablename

WHILE (@@fetch_status = 0)

	BEGIN
		GRANT SELECT
		ON @tablename
		TO UserRpt

		FETCH NEXT FROM @tablepermission
		INTO @tablename
	END

CLOSE @tablepermission
DEALLOCATE @tablepermission

When i try to create this SP i get

Server: Msg 170, Level 15, State 1, Procedure usp_GrantPermissions, Line 27
Line 27: Incorrect syntax near '@tablename'.

Does someone know where i am going wrong please.

Can i do it without a cursor ? I tried this as well....


GRANT SELECT ON
(select name from sysobjects
where type='u')
To UserRpt

.....but to no avail

It's a replicated subscriber DB that certain users can use via an Access FE to query the database (for better or for worse)

thanks

===========
Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 13:26:16

What you are trying to do needs to be done through dynamic sql. Change your GRANT statement to:

EXEC ('GRANT SELECT ON dbo.' + @tablename + ' TO UserRpt')

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 13:31:01

Here is the stored procedure that I use to grant all of the necessary permissions to a particular role (we have naming standards for our database objects so I only grant permissions to objects that follow the standard, thus making developers follow the standard

CREATE PROC isp_Grant_Permissions
AS

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM SYSOBJECTS 
WHERE 	(	
		(type = 'P' AND name LIKE 'usp[_]%') 
		OR 
		(type = 'FN' AND name like 'udf[_]%')
		OR
		(type = 'TF' AND name like 'udf[_]%')
		OR
		(type = 'U')
		OR
		(type = 'V' AND name like 'v[_]%')
	)
	AND 
		uid = 1
	AND
		status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @objType NOT IN ('TF', 'T', 'V')
	BEGIN
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO GT_Apps')
	END

	ELSE
	BEGIN
		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO GT_Apps')	
	END

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 14:36:06

Cool, thanks Tara....snagging this one right in to my sql toolbox!!!

Brett

8-)

PS why do you qualify your sprocs with isp_

Edited by - x002548 on 04/14/2003 14:38:32

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 14:45:35

isp_ are for the internal stored procedures, such as the ones that the DBAs write to administer things. usp_ are the user stored procedures, which are the ones used by the application. It just helps identity which ones are used by the applications and which ones aren't necessarily for any application in particular, such as isp_Grant_Permissions.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 14:52:25

quote:

Can i do it without a cursor ? I tried this as well....

Cursors are very useful for things such as this. Since this isn't used by the application, it really isn't necessary to rewrite it so that it doesn't use a cursor. I use cursors for administrative things such as this. But none of them are called by any applications. They are simply run by me or another DBA manually.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 15:23:21

I would imaghine anytime you write code that calls "code" (like GRANT) that requires parameters you either need to use a cursor, or generate all of the commands, then re execute the batch of generated statements (which is kind of kludgey). I guess you could create a bat file then execute it if you want to automate it.

Tjhat brings up a point. Does anyone have a way to write lines out to a text file?

I currently do something like:

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Starting Quarterly Load Process'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' > d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 15:40:10

What do you mean write lines out to a text file? You can send output of batch files using > (to start a new file) or >> (to append to the file). You can also use -o if you are using osql.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 16:13:57

Yeah, no I know that bit. That's how I log the execution of any batch utility we run.

I'm talking about inside of a sproc. Currently I use what I posted to write a Sproc text log. In this case it gets written

quote:

to:d:\Data\Tax\log_out.txt'

I do this because if there is any rollback in the sproc I want to know what events along the way happened (because it's echoed to the text file). Because if I do insert to the log table they get wiped out when the rollback occurs.

Brett

8-)

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-04-15 : 05:07:02

brilliant thanks

===========
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources