Add new table and have Public Roles automatic..

[DougP]

I create new tables from time to timeand was wondering if there was a way to make the Pulic role the default.

Now I give the user a program only to find out there is an ODBC error "they don't have permissions"
So I come back to the SQL server and open Enterprise manager and set that table to be Public.
The 4 permissions that is (put a check in the box)

Select, Insert, Update, Delete

So when I create a new table I would like the 4 Public permissions above to be set as the table is created.
TIA
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[tlbroadbent]

We could probably devise a way to grant permissions to Public every time a table is created. however, I see a real security nightmare. An easier solution is to make each user a member of the database roles db_datareader and db_datawriter.

As db_datareader they can select from every table and view in the database. As db_datawriter they can insert, update and delete from every table and (updatable) view in the database. Permissions can be denied on each object and/or user if needed.

NOTE: db_datareader and db_datawriter roles do not have permissions on stored procedures. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[SLAbel]

Hello,

No I dont think you can default the permissions to public. The only way I could see would be to grant the permissions at the time you define the table using the GRANT command. For example:

Create Table MyTable
(
pk_SLAbel Integer,
Name VarChar(50)
)
go
Grant Select, Insert, Update, Delete On MyTable To Public
go

Hope that helps
scott

 

[redlam]

Terry:
How do you handle permissions for stored procedures? Just curious... I usually create a new role and assign users to that role. I wrote a stored procedure that uses the sp_MSforeach* logic to grant permissions to that role for each stored procedure but I'm not sure if any of this was necessary - I gave up looking for a better way but could've easily over looked something.

 

[tlbroadbent]

redlam,

I've not found a better way to grant permissions on SPs than the one you use. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[DougP]

Great input people
I think I'll use "SLAbel's" line to make a stored procedure.


Grant Select, Insert, Update, Delete On MyTable To Public

Now how can I make it prompt for a table to be put in the "MyTable" variable

Thanks
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[dbmsguy]

i would think you could use:

MS_foreachtable 'grant bla, bla on "?" mytable'

put this in a stored proc and just run it evertime you create a table. there is no problem granting a table that has already been granted..... Paul

http://www.dbmsguy.com

 

[DougP]

dbmsguy,
That looks good too.
So if I were to add Public permisions
the syntax would be: ????

MS_foreachtable 'grant public on "?" mytable'


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[redlam]

the syntax would be something like this... depending on the permissions being granted.

exec sp_MSforeachtable 'grant select on ? to public'

 

[DougP]

thanks redlam
I'll give it a try
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive