July 6, 2006 at 9:07 am
How to grant select permissions on all tables and views in a a database?
July 6, 2006 at 9:18 am
Execute the following sql statement in query analyzer
select 'grant select on ' + name + ' To login_name' from sysobjects where xtype='U' or xtype ='V'
Copy ,paste and execute the result of this query in QA.
Thanks,
SR
Thanks,
SR
July 6, 2006 at 9:33 am
You can add users to the fixed database role called db_datareader. It can be done either in Enterprise Manager or by executing sp_addrolemember in Query Analyzer.
Greg
Greg
July 6, 2006 at 10:24 am
There is already db_datareader permissions for the database , but when I check the Permissions of that user in that database I don't see select permissions for any table.
Is db_datareader permissions sufficient for selecting data in all tables without explicitly giving select permissions for the tables?
July 6, 2006 at 2:24 pm
Yes. The reason you don't see table permissions for the user is the permission is granted to the role and the user is a member of the role. See 'fixed database roles' in BOL.
Greg
Greg
April 13, 2007 at 10:44 am
I added 'to' to the query as shown below in order to get it to properly run the results in QA.
Tim C.
select 'grant select on ' + name + ' ' + 'to' + 'username'
from sysobjects
where xtype='U' or xtype ='V'
April 13, 2007 at 11:17 am
What I do and recommend the developers to follow is when you create an object always explicitly grant permission on that. For example let's say that everyone will need to select on the view, then the last statement in the CREATE VIEW dbo.MyView1 script is mostly likely
GRANT SELECT ON dbo.MyView1 TO PUBLIC
GO
Good day
Bulent
April 15, 2007 at 7:40 am
Best way (IMHO), if you can, is to have all access to tables via stored procs, whether SELECT, UPDATE, INSERT, or DELETE. This way no-one has access to the physical tables. May be seen as overkill, but its easy to give users permissions to run procs, which in-turn have permissions to access the tables needed. Not sure about views, but don't tend to use them too much as they can get miss-used (which I have seen causing all sorts of performance problems on our db servers until I was able to persuade the developers to change).
HTH
April 23, 2007 at 2:38 pm
Another common way to grant permissions is to create user roles and assign permissions to the role (through Enterprise Manager or Query Analyzer). Then as new people are added you can just add them to the role.
Steve
April 24, 2007 at 6:40 am
I have a script that iterates through all the database objects and runs a grant statement to set user permissions.
Just tweak it to suit - mine currently has all table access commented out as I use stored procs.
/*
Allocate select permission on all tables
*/
declare @Objectvar Varchar(128)
declare @ownervar Varchar(128)
declare @rolepar varchar(20)
/*
Set database name and user/role name
put your own in as appropriate by replacing < to > with yours
*/
use <mydatabasename>
set @rolepar = '<my user or role>'
/*
declare cuKeys cursor
for SELECT dbo.sysobjects.name, dbo.sysusers.name AS owner
FROM dbo.sysobjects INNER JOIN
dbo.sysusers ON dbo.sysobjects.uid = dbo.sysusers.uid
where type = 'U'
open cuKeys
fetch next from cukeys into @Objectvar, @Ownervar
while (@@fetch_status <> -1)
begin
print @Objectvar
exec ('grant select on '+ @Ownervar + '.' + @Objectvar +' to '+ @rolepar)
exec ('grant update on '+ @Ownervar + '.' + @Objectvar +' to '+ @rolepar)
exec ('grant insert on '+ @Ownervar + '.' + @Objectvar +' to '+ @rolepar)
exec ('grant delete on '+ @Ownervar + '.' + @Objectvar +' to '+ @rolepar)
fetch next from cuKeys into @Objectvar, @Ownervar
end
deallocate cukeys
*/
/*
Allocate execute permission on all stored procedures and functions
*/
declare cuKeys cursor
for SELECT dbo.sysobjects.name, dbo.sysusers.name AS owner
FROM dbo.sysobjects INNER JOIN
dbo.sysusers ON dbo.sysobjects.uid = dbo.sysusers.uid
where (type = 'P') or (type = 'FN')
open cuKeys
fetch next from cukeys into @Objectvar, @Ownervar
while (@@fetch_status <> -1)
begin
if left(@Objectvar,3) <> 'dt_'
begin
print @Objectvar
exec ('grant execute on '+ @Ownervar + '.' + @Objectvar +' to '+ @rolepar)
end
fetch next from cuKeys into @Objectvar, @Ownervar
end
deallocate cukeys
/*
Allocate select permission on all views
*/
declare cuKeys cursor
for SELECT dbo.sysobjects.name, dbo.sysusers.name AS owner
FROM dbo.sysobjects INNER JOIN
dbo.sysusers ON dbo.sysobjects.uid = dbo.sysusers.uid
where type = 'V'
open cuKeys
fetch next from cukeys into @Objectvar, @Ownervar
while (@@fetch_status <> -1)
begin
print @Objectvar
exec ('grant select on '+ @Ownervar + '.' + @Objectvar +' to '+ @rolepar)
fetch next from cuKeys into @Objectvar, @Ownervar
end
deallocate cukeys
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply