A "cursor" ?-Building rights script

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)

A "cursor" ?-Building rights script

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2002-12-19 : 13:39:28

Hey SQL'ers
I am trying to develop a relatively generic sql script
that creates a user and gives them standard rights
to the current database for tables, views and sprocs.

I used cursors for simplicity, but the running the sql "red lines" my box and I am not sure why. I was hoping someone could point out my flaw, and perhaps, how I could do this w/o a cursor.

A snapshot of the code:
Set @usr = 'TestNorthwind_User'
Set @pwd = 'ApfenzellerBier'

Exec sp_addlogin @usr ,@pwd
EXEC sp_grantdbaccess @usr, @usr

-- ********* Grant rights on tables *********************
Set @str = ''
Declare curs CURSOR For
select [name] from sysobjects where xtype = 'U'
OPEN curs
FETCH NEXT FROM curs
INTO @tbl
WHILE @@FETCH_STATUS = 0
BEGIN
Set @str = (@str + ' Grant Select,Insert,Update,Delete on ' + @tbl + ' to ' + @usr)
END
FETCH NEXT FROM curs
INTO @tbl
CLOSE curs
DEALLOCATE curs

if(@str <> '')
Exec sp_executesql @str

_________________________
Beer is healthy, I read it on the internet. It must be true!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-19 : 13:48:28

You shouldn't be giving permissions to a particular user. Instead, you should be giving permissions to a role and then adding the user to the role. So you will only have to setup permissions on the role and not on each individual user.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-19 : 13:52:13

How big is @str?
you would be better off executing the grants individually.

Why not create a role with these permissions then add the users to the role?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2002-12-19 : 14:02:49

Okay,

I create a role, create the user, and add the user.

How do I grant the rights to the role then?

Thanks
Danke Schoen
Gracias
Tak

_________________________
Beer is healthy, I read it on the internet. It must be true!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-19 : 14:16:27

Well you shouldn't just go through and grant select, insert, update, and delete to all of the tables. You should analyze your code and figure out which permissions are needed. The easiest to do grant the permissions would be to use Enterprise Manager. Just go to the role and click permissions and start setting up the permissions that you need. You don't need code to do this because you only have to do this once. If you add a table, you will need to go to that role and grant additional permissions. This is the recommended approach for SQL security.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2002-12-19 : 14:23:03

Well,

When you are utilizing Sql Server via a Web App, I not too sure that I want to attempt to manage many sql server users and the 100's of varieties of security configurations, as I prefer to use application security and not sql server so much.

Needless to say, I am trying to make a script version of a rights granting for easy and quick deployment to multiple machines (demos,testing) as actual security is more client dependent, versus application dependent.

Is it possible to do this in script?
I do know how to do this in Enterprise Manager, but I wanted to make something I could use for all databases and would be quick and simple for all the developers to use.

quote:

Well you shouldn't just go through and grant select, insert, update, and delete to all of the tables. You should analyze your code and figure out which permissions are needed.

_________________________
Beer is healthy, I read it on the internet. It must be true!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-19 : 14:46:38

Why not just make it dbo then?

Have you thought about using NT security and inheriting it from the NT groups? (Assuming you are NT environment).

What you are doing should work for the role but execute the grant statements individually rather than in a single string - puts less strain on the server and doesn't limit you to the number of objects you can grant to (you probably had a limit of about 100 with your script and may have been the problem).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources