Grant Permission to Modify Views

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 2005 Forums

SQL Server Administration (2005)

Grant Permission to Modify Views

Author

Topic

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2010-07-15 : 09:09:11

Hello,

I need to give two SQL developers permission to create and modify views in a specific database. This is what I did but it does not seem to be working.

* SQL Server 2005 Ent with SP3

1. In the database I created a new role named "Modify Views".

2. On the SQL server I created a new SQL login. The login mapped to an Active Directory security group which contains the two users I want to have access.

3. Under User Mapping I mapped the new SQL login to the database with Public and Modify Views membership.

4. In SQL Management Studio I opened a new query window and selected the database I wanted to modify. I ran the following query:

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select, insert, update, delete, alter, control, view definition on [' + convert(varchar(64),name) + '] to [Modify Views]' from sysobjects
where (type = 'V') and uid = 1

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

Any help will be greatly appreciated!

Regards,
Terry

rdjabarov
Starting Member

8 Posts

Posted - 2010-07-15 : 15:17:50

To ensure that there is no confusion on object and schema, I always perform such queries against sys.objects, and I construct a fully qualified object name:

'[' + schema_name(schema_id) + '].[' + [name] + ']'

"The data in a record depends on the Key to the record, the Whole Key, and nothing but the Key, so help me Codd."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources