I've been tediously working on setting up scripts to limit access through
our different departments accordingly. I've been working at creating the
tightest set of perms first but can't seem to get it working. I've posted
the script obviously but figured I'd ask if there were some way to determine
what permissions are required when I get an access denied error...
In the script it is assumed that there are already 3 logins created and
added to the current database. I know there is alot to go on the script as
far as error checking, previous existence etc....but in a controlled
environment (my localhost at this time) this should work. The problem is
after running this I couldn't even open and view db properties...I had to
drop and recreate the user from login. Then I still couldn't select data
from any table.
THANKS!!!! For any input. I know I'm missing it somewhere here bigtime.
----
--
-- LIVE_LimitedWebAccess --
-- creates permissions applicable to a live environment for web
----
--
----
--
-- create the role that will be used to limit access
----
--
CREATE ROLE [LimitedWebAccess] AUTHORIZATION [dbo]
----
--
-- add logins to the new role
----
--
EXEC sp_addrolemember N'LimitedWebAccess', N'web'
EXEC sp_addrolemember N'LimitedWebAccess', N'webdev'
EXEC sp_addrolemember N'LimitedWebAccess', N'wtf'
----
--
-- loop thru all tables setting permissions appropriately for the new role
----
--
declare @.strName nvarchar(100)
declare @.strSQL nvarchar(1024)
declare curTables cursor for
select [name] from sys.tables where type='U'
open curTables
fetch next from curTables into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curTables into @.strName
end
close curTables
deallocate curTables
----
--
-- loop thru all views setting permissions appropriately for the new role
----
--
declare curViews cursor for
select [name] from sys.views
open curViews
fetch next from curViews into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curViews into @.strName
end
close curViews
deallocate curViews
----
--
-- loop thru all stored procs setting permissions appropriately for the new
role
----
--
declare curProcs cursor for
select [name] from sys.procedures where is_ms_shipped=0
open curProcs
fetch next from curProcs into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curProcs into @.strName
end
close curProcs
deallocate curProcs
----
--
-- loop thru all user functions setting permissions appropriately for the
new role
----
--
declare curFuncs cursor for
select [name] from sys.objects where type='FN'
open curFuncs
fetch next from curFuncs into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curFuncs into @.strName
end
close curFuncs
deallocate curFuncs> the script obviously but figured I'd ask if there were some way to
> determine what permissions are required when I get an access denied
> error...
I have a couple questions,
Does the login have sys_admin privileges?
does the user have db_owner fixed database role privileges?
Have you issued GRANT ON SELECT.... for more details please refer to the
BOL
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:e$gFbWPiGHA.3296@.TK2MSFTNGP05.phx.gbl...
> I've been tediously working on setting up scripts to limit access through
> our different departments accordingly. I've been working at creating the
> tightest set of perms first but can't seem to get it working. I've posted
> the script obviously but figured I'd ask if there were some way to
> determine what permissions are required when I get an access denied
> error...
> In the script it is assumed that there are already 3 logins created and
> added to the current database. I know there is alot to go on the script
> as far as error checking, previous existence etc....but in a controlled
> environment (my localhost at this time) this should work. The problem is
> after running this I couldn't even open and view db properties...I had to
> drop and recreate the user from login. Then I still couldn't select data
> from any table.
> THANKS!!!! For any input. I know I'm missing it somewhere here bigtime.
> ----
--
> -- LIVE_LimitedWebAccess --
> -- creates permissions applicable to a live environment for web
> ----
--
>
> ----
--
> -- create the role that will be used to limit access
> ----
--
> CREATE ROLE [LimitedWebAccess] AUTHORIZATION [dbo]
> ----
--
> -- add logins to the new role
> ----
--
> EXEC sp_addrolemember N'LimitedWebAccess', N'web'
> EXEC sp_addrolemember N'LimitedWebAccess', N'webdev'
> EXEC sp_addrolemember N'LimitedWebAccess', N'wtf'
>
> ----
--
> -- loop thru all tables setting permissions appropriately for the new role
> ----
--
> declare @.strName nvarchar(100)
> declare @.strSQL nvarchar(1024)
> declare curTables cursor for
> select [name] from sys.tables where type='U'
> open curTables
> fetch next from curTables into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curTables into @.strName
> end
> close curTables
> deallocate curTables
>
> ----
--
> -- loop thru all views setting permissions appropriately for the new role
> ----
--
> declare curViews cursor for
> select [name] from sys.views
> open curViews
> fetch next from curViews into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curViews into @.strName
> end
> close curViews
> deallocate curViews
>
> ----
--
> -- loop thru all stored procs setting permissions appropriately for the
> new role
> ----
--
> declare curProcs cursor for
> select [name] from sys.procedures where is_ms_shipped=0
> open curProcs
> fetch next from curProcs into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curProcs into @.strName
> end
> close curProcs
> deallocate curProcs
>
> ----
--
> -- loop thru all user functions setting permissions appropriately for the
> new role
> ----
--
> declare curFuncs cursor for
> select [name] from sys.objects where type='FN'
> open curFuncs
> fetch next from curFuncs into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curFuncs into @.strName
> end
> close curFuncs
> deallocate curFuncs
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFFpF6SiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> I have a couple questions,
> Does the login have sys_admin privileges?
> does the user have db_owner fixed database role privileges?
Do you mean for the user running the script? If so I'm running the script
as 'sa'. If you mean the user 'web' then absolutely not. We want to
restrict them to selecting/updating data. As shown in the script below they
are explicitly granted select on all tables/views...
> Have you issued GRANT ON SELECT.... for more details please refer to the
> BOL
I have. That is how I got this far...but I'm having trouble now finding
what is missing. I don't want the user 'web' to have sys_admin privileges.
But with the script below the user 'web' now gets 'Invalid object name
'tblname'' when trying to select data from a table.
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:e$gFbWPiGHA.3296@.TK2MSFTNGP05.phx.gbl...
>|||Tim
> But with the script below the user 'web' now gets 'Invalid object name
> 'tblname'' when trying to select data from a table.
Pobably because the "web" user/login is not the owner of the "tblname", Can
you check it?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:eoG8BuXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eFFpF6SiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> Do you mean for the user running the script? If so I'm running the script
> as 'sa'. If you mean the user 'web' then absolutely not. We want to
> restrict them to selecting/updating data. As shown in the script below
> they are explicitly granted select on all tables/views...
>
>
> I have. That is how I got this far...but I'm having trouble now finding
> what is missing. I don't want the user 'web' to have sys_admin
> privileges. But with the script below the user 'web' now gets 'Invalid
> object name 'tblname'' when trying to select data from a table.
>
>
>
