Permissions

[Steffi]

When I restore a database, I lose login permission and role permissions. I end up going
into the login and unselect and reselect the database to make sure that the login works
with the database. Is there a way to get around this??

One other question:: I have added 65 new tables to a database and want to give select
only to all logins to those specific table -- is there an easy way of doing this???

[Adam Brown]

Steffi,
When you drop the user from the database you lose all the permissions associated with that user. Instead of dropping and adding all the logins and roles put this procedure in the master database (or somewhere else) and run it. It will remapp the sysusers table in the database to match the uid in the syslogins table. This will keep the permissions for the users and I believe for your roles too because they are also stored in the sysusers table.

CREATE PROCEDURE proc_fix_database1_logins
AS
BEGIN
BEGIN
EXEC sp_configure 'allow updates', 1
END
BEGIN
RECONFIGURE WITH OVERRIDE
END

BEGIN
UPDATE database1.dbo.sysusers
SET database1.dbo.sysusers.sid = sl.sid
FROM database1.dbo.sysusers su JOIN master.dbo.syslogins sl
ON su.name = sl.name
END

BEGIN
EXEC sp_configure 'allow updates', 0
END
BEGIN
RECONFIGURE WITH OVERRIDE
END
END

In answer to the second part of your question I believe there is a really good article on the sp_MSforeachtable procedure that will help you grant select to a ton of tables. Or you can create a cursor by date in the sysobjects table and use that to grant select to everyone.

Hope this helps,

Adam

------------
Steffi at 6/22/00 11:29:06 PM

When I restore a database, I lose login permission and role permissions. I end up going
into the login and unselect and reselect the database to make sure that the login works
with the database. Is there a way to get around this??

One other question:: I have added 65 new tables to a database and want to give select
only to all logins to those specific table -- is there an easy way of doing this???