DECLARE @objname varchar(150), @type varchar(10), @usr varchar(20), @sql nvarchar(100);
SET @usr = "tempdwlogin';
DECLARE read_only_cur CURSOR FOR
SELECT sc.name + '.' + ob.name, ob.type
FROM sys.objects ob, sys.schemas sc
WHERE ob.schema_id = sc.schema_id
AND ob.type IN ('U', 'V', 'FN', 'IF', 'TF', 'FS', 'FT','P', 'PC');
OPEN read_only_cur;
FETCH NEXT FROM read_only_cur
INTO @objname, @type;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type IN ( 'U', 'V')
BEGIN
SET @sql = N'GRANT SELECT ON ' + @objname + N'TO ' + @usr;
EXEC sp_executesql @sql;
END
ELSE
BEGIN
SET @sql = N'GRANT EXECUTE ON ' + @objname + N'TO ' + @usr;
EXEC sp_executesql @sql;
END
FETCH NEXT FROM read_only_cur;
END;
CLOSE read_only_cur;
DEALLOCATE read_only_cur;
ASKER
DECLARE @objname varchar(150), @type varchar(10), @usr varchar(20), @sql nvarchar(100);
SET @usr = 'tempdwlogin';
DECLARE read_only_cur CURSOR FOR
SELECT sc.name + '.' + ob.name, ob.type
FROM sys.objects ob, sys.schemas sc
WHERE ob.schema_id = sc.schema_id
AND ob.type IN ('U', 'V', 'FN', 'IF', 'TF', 'FS', 'FT','P', 'PC');
OPEN read_only_cur;
FETCH NEXT FROM read_only_cur
INTO @objname, @type;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type IN ( 'U', 'V')
BEGIN
SET @sql = N'GRANT SELECT ON ' + @objname + N'TO ' + 'tempdwlogin';
EXEC sp_executesql @sql;
END
ELSE
BEGIN
SET @sql = N'GRANT EXECUTE ON ' + @objname + N'TO ' + 'tempdwlogin';
EXEC sp_executesql @sql;
END
FETCH NEXT FROM read_only_cur INTO @objname, @type
END;
CLOSE read_only_cur;
DEALLOCATE read_only_cur;
ASKER
ASKER
DECLARE @objname varchar(150), @type varchar(10), @usr varchar(20), @sql nvarchar(100);
SET @usr = 'tempdwlogin';
DECLARE read_only_cur CURSOR FOR
SELECT sc.name + '.' + ob.name, ob.type
FROM sys.objects ob, sys.schemas sc
WHERE ob.schema_id = sc.schema_id
AND ob.type IN ('U', 'V', 'FN', 'IF', 'TF', 'FS', 'FT','P', 'PC');
OPEN read_only_cur;
FETCH NEXT FROM read_only_cur
INTO @objname, @type;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type IN ( 'U', 'V')
BEGIN
SET @sql = N'GRANT SELECT ON ' + @objname + N'TO ' + @usr;
EXEC sp_executesql @sql;
END
ELSE
BEGIN
SET @sql = N'GRANT EXECUTE ON ' + @objname + N'TO ' + @usr;
EXEC sp_executesql @sql;
END
FETCH NEXT FROM read_only_cur INTO @objname, @type
END;
CLOSE read_only_cur;
DEALLOCATE read_only_cur;
ASKER
use master;
SELECT *
FROM sys.sysusers
WHERE name like 'MyTest%' ;
go
create login MyTestLogin with password = '123MyTestLogin123';
go
create user MyTestUser for login MyTestLogin;
go
use EE;
go
GRANT SELECT ON dbo.tmpempid TO MyTestLogin;
go
-- the above fails with the same error as you get.
--Msg 15151, Level 16, State 1, Line 58
--Cannot find the user 'MyTestLogin', because it does not exist or you do not have permission.
-- Need to assign permissions to USER
GRANT SELECT ON dbo.tmpempid TO MyTestUser;
go
-- now clean up the testing
use master;
go
drop user MyTestUser;
go
drop login MyTestLogin;
go
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
SET @usr = "tempdwlogin';
try:
Open in new window