Had an interesting issue after restoring a database to SQL Server 2008 database server. None of the users could select from any tables. When restoring a SQL Server database, you pretty much always have to go in and recreate the accounts and add them to the databases again with the appropriate permissions. This was a bit different and odd.
Here's how I solved it:
1. Tried adding a select role which had select permissions on the database (did not solve it but here's now to do it):
Let's say your user name is "dbuser" and you want to create some roles to do different things like execute, insert, select, etc. You could do something like this - which is probably overkill and you might want to set up a different schema also - but I was just testing anyway:
/* CREATE ROLES */
CREATE ROLE db_execute
CREATE ROLE db_select
CREATE ROLE db_delete
CREATE ROLE db_insert
CREATE ROLE db_update
/* GRANT PERMISSIONS TO ROLES */
GRANT EXECUTE ON SCHEMA :: dbo TO db_execute
GRANT SELECT ON SCHEMA :: dbo TO db_select
GRANT DELETE ON SCHEMA :: dbo TO db_delete
GRANT INSERT ON SCHEMA :: dbo TO db_insert
GRANT UPDATE ON SCHEMA :: dbo TO db_update
/* ADD USER TO ROLES */
EXEC sp_addrolemember 'db_execute', 'dbuser'
EXEC sp_addrolemember 'db_update', 'dbuser'
EXEC sp_addrolemember 'db_insert', 'dbuser'
EXEC sp_addrolemember 'db_delete', 'dbuser'
EXEC sp_addrolemember 'db_select', 'dbuser'
2. You could grant permission to all tables and views in your database like this handy dandy script I found here
use MYDBNAME
DECLARE @login varchar(50)
SET @login = 'dbuser'
DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @tables
SELECT 'GRANT SELECT ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'U'
AND NAME NOT LIKE 'SYNC%'
DECLARE @rowid int, @sqlstr varchar(500)
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_tbl_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @tables
ORDER BY ROWID
OPEN grant_tbl_cursor
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_tbl_cursor
DEALLOCATE grant_tbl_cursor
DECLARE @views TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @views
SELECT 'GRANT SELECT ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'V'
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_vw_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @views
ORDER BY ROWID
OPEN grant_vw_cursor
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
END
3. But what really solved the problem for me was looking at the effective permissions on the table that I initially tested. Right click, choose properties and check out the effective permissions tab. I noticed that the public role was denied pretty much any permission on any database object. Given that all users must be in the public role and cannot be removed, had to fix that.
First I verified that these permissions were also returning from a query on the permissions and users for that object:
select OBJECT_NAME(major_id) as object,
USER_NAME(grantee_principal_id) as grantee,
USER_NAME(grantor_principal_id) as grantor,
Permission_name,
state_desc, class
from sys.database_permissions
where major_id=OBJECT_ID('Table_In_Question')
Sure enough, public had deny set on just about everything.
So...I undenied it but removing all permissions from the public role and resetting permissions as desired.
REVOKE SELECT FROM PUBLIC
It's a little odd that we are revoking the denying of something, but that's how it works. Running my permissions query after this showed that the public user had no object level select priveledges on that table - neither grant nor deny. I tested and was able to select from the table with the other user that was part of the public role.
I also tested after my changes and that same user was not able to change permissions or table strucures (which is what I was after in the first place via more granular database permissions.) Further review of system tables and probably schema changes are in order, but this particular problem is solved.
