Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?
|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?
|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fixed Database Roles have Disappeared From Enterprise Manager:Security:Server Roles

I'm afraid the answer to this question will fall into the "disaster
recovery" area...one in which I am woefully lacking.
OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
and one for production. I noticed a couple of weeks ago that the Fixed
Server Roles on the QA machine had suddenly disappeared. Everything seemed
to be performing ok, so I didn't give it much thought at the time...oops!
Now I am noticing that scheduled jobs have started failing on that machine
and I'm not sure why. (Side question: where exactly does one find the SQL
Server Logs that failures are supposedly written to?)
Where would these roles have gone to? Why would they have gone there? How
do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive and
reinstall SQL Server......while this isn't the production server - Thank
all that is holy! - the database backups on it have been failing...)
When I run sp_helprole I get:
public 0 0
db_owner 16384 0
db_accessadmin 16385 0
db_securityadmin 16386 0
db_ddladmin 16387 0
db_backupoperator 16389 0
db_datareader 16390 0
db_datawriter 16391 0
db_denydatareader 16392 0
db_denydatawriter 16393 0
When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
tab I get:
(0 row(s) affected)
...when I run sp_helpdbfixedrole on another server I get:
db_accessadmin DB Access Administrators
db_backupoperator DB Backup Operator
db_datareader DB Data Reader
db_datawriter DB Data Writer
db_ddladmin DB DDL Administrators
db_denydatareader DB Deny Data Reader
db_denydatawriter DB Deny Data Writer
db_owner DB Owners
db_securityadmin DB Security Administrators
when I run sp_helprolemember, I only get: db_owner dbo 0x01
when I run sp_helpdb, the db_size column for each database on this server is
null...
TIA,
Rox> and I'm not sure why. (Side question: where exactly does one find the
SQL
> Server Logs that failures are supposedly written to?)
Please ignore the side question...I found them, but they don't seem to have
anything to do with this problem...
Rox|||So what roles seemed to have disappeared? You say that the
fixed server roles disappeared but you are indicating issues
with and running stored procedures to look at the fixed
database roles?
What are the details for the errors on the jobs that are
failing? What are the error details for the backups that are
failing?
Have you run DBCCs on the databases?
-Sue
On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>I'm afraid the answer to this question will fall into the "disaster
>recovery" area...one in which I am woefully lacking.
>OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
>and one for production. I noticed a couple of weeks ago that the Fixed
>Server Roles on the QA machine had suddenly disappeared. Everything seemed
>to be performing ok, so I didn't give it much thought at the time...oops!
>Now I am noticing that scheduled jobs have started failing on that machine
>and I'm not sure why. (Side question: where exactly does one find the SQL
>Server Logs that failures are supposedly written to?)
>Where would these roles have gone to? Why would they have gone there? How
>do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive an
d
>reinstall SQL Server......while this isn't the production server - Thank
>all that is holy! - the database backups on it have been failing...)
>When I run sp_helprole I get:
>public 0 0
>db_owner 16384 0
>db_accessadmin 16385 0
>db_securityadmin 16386 0
>db_ddladmin 16387 0
>db_backupoperator 16389 0
>db_datareader 16390 0
>db_datawriter 16391 0
>db_denydatareader 16392 0
>db_denydatawriter 16393 0
>When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
>tab I get:
>(0 row(s) affected)
>...when I run sp_helpdbfixedrole on another server I get:
>db_accessadmin DB Access Administrators
>db_backupoperator DB Backup Operator
>db_datareader DB Data Reader
>db_datawriter DB Data Writer
>db_ddladmin DB DDL Administrators
>db_denydatareader DB Deny Data Reader
>db_denydatawriter DB Deny Data Writer
>db_owner DB Owners
>db_securityadmin DB Security Administrators
>when I run sp_helprolemember, I only get: db_owner dbo 0x01
>when I run sp_helpdb, the db_size column for each database on this server i
s
>null...
>TIA,
>Rox
>|||> So what roles seemed to have disappeared? You say that the
> fixed server roles disappeared but you are indicating issues
> with and running stored procedures to look at the fixed
> database roles?
> What are the details for the errors on the jobs that are
> failing? What are the error details for the backups that are
> failing?
The fixed roles don't show up in Enterprise Manager or using
sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
doesn't have permissions. I managed to backup the most important DBs
manually (assuming that they are indeed good...I haven't tried restoring
them yet.

> Have you run DBCCs on the databases?
I've run DBCC CheckDatabase on all of the databases (including Master) with
no errors found.
Any ideas that you have would be appreciated.
Thanks,
Rox

> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
>
QA;[vbcol=seagreen]
seemed[vbcol=seagreen]
machine[vbcol=seagreen]
SQL[vbcol=seagreen]
How[vbcol=seagreen]
and[vbcol=seagreen]
Thank[vbcol=seagreen]
messages[vbcol=seagreen]
is[vbcol=seagreen]
>|||I have a feeling that it is time for MS support in your case. I've looked ar
ound and I didn't find any system
table where the fixed server roles are stored (but I've might have missed it
, of course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK2MSFTNGP12.phx.gbl
..
> The fixed roles don't show up in Enterprise Manager or using
> sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
> doesn't have permissions. I managed to backup the most important DBs
> manually (assuming that they are indeed good...I haven't tried restoring
> them yet.
>
> I've run DBCC CheckDatabase on all of the databases (including Master) wit
h
> no errors found.
> Any ideas that you have would be appreciated.
> Thanks,
> Rox
>
> QA;
> seemed
> machine
> SQL
> How
> and
> Thank
> messages
> is
>|||For server roles, try executing the following in Query
Analyzer and see if the roles are displayed:
select v1.name as 'Server Role',
v2.name as 'Role Description'
from master.dbo.spt_values v1, master.dbo.spt_values v2
where v1.low = 0 and
v1.type = 'SRV' and
v2.low = -1 and
v2.type = 'SRV' and
v1.number = v2.number
For database roles, try executing the following in Query
analyzer and see if the roles are displayed:
select name as 'Role Name',
uid as 'Role ID',
isapprole as 'Is Application Role'
from sysusers
where issqlrole = 1 or isapprole = 1
-Sue
On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>The fixed roles don't show up in Enterprise Manager or using
>sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
>doesn't have permissions. I managed to backup the most important DBs
>manually (assuming that they are indeed good...I haven't tried restoring
>them yet.
>
>I've run DBCC CheckDatabase on all of the databases (including Master) with
>no errors found.
>Any ideas that you have would be appreciated.
>Thanks,
>Rox
>
>QA;
>seemed
>machine
>SQL
>How
>and
>Thank
>messages
>is
>|||Seems like Sue might have nailed it. The procedure sp_helpdbfixedrole does i
ndeed to a join against spt_values
so it is likely that non-existence of these rows in spt_values will give you
the behavior you see...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uh7sJPFPEHA.3896@.TK2MSFTNGP12.phx.gbl...
> I have a feeling that it is time for MS support in your case. I've looked around a
nd I didn't find any
system
> table where the fixed server roles are stored (but I've might have missed
it, of course).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK
2MSFTNGP12.phx.gbl...
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.
4ax.com...
> For server roles, try executing the following in Query
> Analyzer and see if the roles are displayed:
> select v1.name as 'Server Role',
> v2.name as 'Role Description'
> from master.dbo.spt_values v1, master.dbo.spt_values v2
> where v1.low = 0 and
> v1.type = 'SRV' and
> v2.low = -1 and
> v2.type = 'SRV' and
> v1.number = v2.number
This query returns 0 rows

> For database roles, try executing the following in Query
> analyzer and see if the roles are displayed:
> select name as 'Role Name',
> uid as 'Role ID',
> isapprole as 'Is Application Role'
> from sysusers
> where issqlrole = 1 or isapprole = 1
This query returns 10 rows as it should.
I guess the next step really is contacting Microsoft...or re-installing SQL
Server... Thanks for your help.
Rox

> -Sue
> On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
>
with[vbcol=seagreen]
for[vbcol=seagreen]
Fixed[vbcol=seagreen]
time...oops![vbcol=seagreen]
the[vbcol=seagreen]
drive[vbcol=seagreen]
server[vbcol=seagreen]
>|||Sue,
OK, I'm looking closer at master.dbo.spt_values...which is empty on that
server. The production server has lots of stuff in it...I don't think I
dare even look in this table on the production server...lol
So what is supposed to be in the spt_values table? Should I look in any
backups I have of Master to see if I can locate a time when this table
actually had values and try to restore them? Or will doing that bring the
server crashing to its knees? I've been looking in all of my SQL books
(haven't tried the web yet) and can't find anything about this table...can
you point me in the direction of more info?
Thanks,
Rox
"R Goodman" <bobnrox@.verizon.net> wrote in message
news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.
4ax.com...
> This query returns 0 rows
>
>
> This query returns 10 rows as it should.
> I guess the next step really is contacting Microsoft...or re-installing
SQL
> Server... Thanks for your help.
> Rox
>
>
restoring[vbcol=seagreen]
> with
> for
> Fixed
> time...oops!
> the
there?[vbcol=seagreen]
> drive
> server
>|||I don't think spt_values is officially documented in any
Microsoft docs. It's mentioned in some articles and books -
you can find some information in this article:
http://www.winnetmag.com/SQLServer/.../8415/8415.html
and this FAQ post:
http://www.mssqlserver.com/faq/general-sptvalues.asp
I can't remember off the top of my head which books have
more information on it. I'd check those by Kalen Delaney and
Ken Henderson as they'd be most likely to have info on the
table.
But It's essentially just a large lookup table used by SQL
Server functions, stored procedures, etc. With no rows in
the table, you are a bit hosed.
You could do a restore from when you had data in the table.
-Sue
On Tue, 18 May 2004 15:51:13 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>Sue,
>OK, I'm looking closer at master.dbo.spt_values...which is empty on that
>server. The production server has lots of stuff in it...I don't think I
>dare even look in this table on the production server...lol
>So what is supposed to be in the spt_values table? Should I look in any
>backups I have of Master to see if I can locate a time when this table
>actually had values and try to restore them? Or will doing that bring the
>server crashing to its knees? I've been looking in all of my SQL books
>(haven't tried the web yet) and can't find anything about this table...can
>you point me in the direction of more info?
>Thanks,
>Rox
>
>
>"R Goodman" <bobnrox@.verizon.net> wrote in message
>news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
>SQL
>restoring
>there?
>

Fixed Database Roles have Disappeared From Enterprise Manager:Security:Server Roles

I'm afraid the answer to this question will fall into the "disaster
recovery" area...one in which I am woefully lacking.
OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
and one for production. I noticed a couple of weeks ago that the Fixed
Server Roles on the QA machine had suddenly disappeared. Everything seemed
to be performing ok, so I didn't give it much thought at the time...oops!
Now I am noticing that scheduled jobs have started failing on that machine
and I'm not sure why. (Side question: where exactly does one find the SQL
Server Logs that failures are supposedly written to?)
Where would these roles have gone to? Why would they have gone there? How
do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive and
reinstall SQL Server......while this isn't the production server - Thank
all that is holy! - the database backups on it have been failing...)
When I run sp_helprole I get:
public 0 0
db_owner 16384 0
db_accessadmin 16385 0
db_securityadmin 16386 0
db_ddladmin 16387 0
db_backupoperator 16389 0
db_datareader 16390 0
db_datawriter 16391 0
db_denydatareader 16392 0
db_denydatawriter 16393 0
When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
tab I get:
(0 row(s) affected)
...when I run sp_helpdbfixedrole on another server I get:
db_accessadmin DB Access Administrators
db_backupoperator DB Backup Operator
db_datareader DB Data Reader
db_datawriter DB Data Writer
db_ddladmin DB DDL Administrators
db_denydatareader DB Deny Data Reader
db_denydatawriter DB Deny Data Writer
db_owner DB Owners
db_securityadmin DB Security Administrators
when I run sp_helprolemember, I only get: db_owner dbo 0x01
when I run sp_helpdb, the db_size column for each database on this server is
null...
TIA,
Rox
> and I'm not sure why. (Side question: where exactly does one find the
SQL
> Server Logs that failures are supposedly written to?)
Please ignore the side question...I found them, but they don't seem to have
anything to do with this problem...
Rox
|||So what roles seemed to have disappeared? You say that the
fixed server roles disappeared but you are indicating issues
with and running stored procedures to look at the fixed
database roles?
What are the details for the errors on the jobs that are
failing? What are the error details for the backups that are
failing?
Have you run DBCCs on the databases?
-Sue
On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>I'm afraid the answer to this question will fall into the "disaster
>recovery" area...one in which I am woefully lacking.
>OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
>and one for production. I noticed a couple of weeks ago that the Fixed
>Server Roles on the QA machine had suddenly disappeared. Everything seemed
>to be performing ok, so I didn't give it much thought at the time...oops!
>Now I am noticing that scheduled jobs have started failing on that machine
>and I'm not sure why. (Side question: where exactly does one find the SQL
>Server Logs that failures are supposedly written to?)
>Where would these roles have gone to? Why would they have gone there? How
>do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive and
>reinstall SQL Server......while this isn't the production server - Thank
>all that is holy! - the database backups on it have been failing...)
>When I run sp_helprole I get:
>public 0 0
>db_owner 16384 0
>db_accessadmin 16385 0
>db_securityadmin 16386 0
>db_ddladmin 16387 0
>db_backupoperator 16389 0
>db_datareader 16390 0
>db_datawriter 16391 0
>db_denydatareader 16392 0
>db_denydatawriter 16393 0
>When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
>tab I get:
>(0 row(s) affected)
>...when I run sp_helpdbfixedrole on another server I get:
>db_accessadmin DB Access Administrators
>db_backupoperator DB Backup Operator
>db_datareader DB Data Reader
>db_datawriter DB Data Writer
>db_ddladmin DB DDL Administrators
>db_denydatareader DB Deny Data Reader
>db_denydatawriter DB Deny Data Writer
>db_owner DB Owners
>db_securityadmin DB Security Administrators
>when I run sp_helprolemember, I only get: db_owner dbo 0x01
>when I run sp_helpdb, the db_size column for each database on this server is
>null...
>TIA,
>Rox
>
|||> So what roles seemed to have disappeared? You say that the
> fixed server roles disappeared but you are indicating issues
> with and running stored procedures to look at the fixed
> database roles?
> What are the details for the errors on the jobs that are
> failing? What are the error details for the backups that are
> failing?
The fixed roles don't show up in Enterprise Manager or using
sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
doesn't have permissions. I managed to backup the most important DBs
manually (assuming that they are indeed good...I haven't tried restoring
them yet.

> Have you run DBCCs on the databases?
I've run DBCC CheckDatabase on all of the databases (including Master) with
no errors found.
Any ideas that you have would be appreciated.
Thanks,
Rox
[vbcol=seagreen]
> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
QA;[vbcol=seagreen]
seemed[vbcol=seagreen]
machine[vbcol=seagreen]
SQL[vbcol=seagreen]
How[vbcol=seagreen]
and[vbcol=seagreen]
Thank[vbcol=seagreen]
messages[vbcol=seagreen]
is
>
|||I have a feeling that it is time for MS support in your case. I've looked around and I didn't find any system
table where the fixed server roles are stored (but I've might have missed it, of course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK2MSFTNGP12.phx.gbl...
> The fixed roles don't show up in Enterprise Manager or using
> sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
> doesn't have permissions. I managed to backup the most important DBs
> manually (assuming that they are indeed good...I haven't tried restoring
> them yet.
>
> I've run DBCC CheckDatabase on all of the databases (including Master) with
> no errors found.
> Any ideas that you have would be appreciated.
> Thanks,
> Rox
>
> QA;
> seemed
> machine
> SQL
> How
> and
> Thank
> messages
> is
>
|||For server roles, try executing the following in Query
Analyzer and see if the roles are displayed:
select v1.name as 'Server Role',
v2.name as 'Role Description'
from master.dbo.spt_values v1, master.dbo.spt_values v2
where v1.low = 0 and
v1.type = 'SRV' and
v2.low = -1 and
v2.type = 'SRV' and
v1.number = v2.number
For database roles, try executing the following in Query
analyzer and see if the roles are displayed:
select name as 'Role Name',
uid as 'Role ID',
isapprole as 'Is Application Role'
from sysusers
where issqlrole = 1 or isapprole = 1
-Sue
On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>The fixed roles don't show up in Enterprise Manager or using
>sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
>doesn't have permissions. I managed to backup the most important DBs
>manually (assuming that they are indeed good...I haven't tried restoring
>them yet.
>
>I've run DBCC CheckDatabase on all of the databases (including Master) with
>no errors found.
>Any ideas that you have would be appreciated.
>Thanks,
>Rox
>
>QA;
>seemed
>machine
>SQL
>How
>and
>Thank
>messages
>is
>
|||Seems like Sue might have nailed it. The procedure sp_helpdbfixedrole does indeed to a join against spt_values
so it is likely that non-existence of these rows in spt_values will give you the behavior you see...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uh7sJPFPEHA.3896@.TK2MSFTNGP12.phx.gbl...
> I have a feeling that it is time for MS support in your case. I've looked around and I didn't find any
system
> table where the fixed server roles are stored (but I've might have missed it, of course).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK2MSFTNGP12.phx.gbl...
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.4ax.com...
> For server roles, try executing the following in Query
> Analyzer and see if the roles are displayed:
> select v1.name as 'Server Role',
> v2.name as 'Role Description'
> from master.dbo.spt_values v1, master.dbo.spt_values v2
> where v1.low = 0 and
> v1.type = 'SRV' and
> v2.low = -1 and
> v2.type = 'SRV' and
> v1.number = v2.number
This query returns 0 rows

> For database roles, try executing the following in Query
> analyzer and see if the roles are displayed:
> select name as 'Role Name',
> uid as 'Role ID',
> isapprole as 'Is Application Role'
> from sysusers
> where issqlrole = 1 or isapprole = 1
This query returns 10 rows as it should.
I guess the next step really is contacting Microsoft...or re-installing SQL
Server... Thanks for your help.
Rox
[vbcol=seagreen]
> -Sue
> On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
with[vbcol=seagreen]
for[vbcol=seagreen]
Fixed[vbcol=seagreen]
time...oops![vbcol=seagreen]
the[vbcol=seagreen]
drive[vbcol=seagreen]
server
>
|||Sue,
OK, I'm looking closer at master.dbo.spt_values...which is empty on that
server. The production server has lots of stuff in it...I don't think I
dare even look in this table on the production server...lol
So what is supposed to be in the spt_values table? Should I look in any
backups I have of Master to see if I can locate a time when this table
actually had values and try to restore them? Or will doing that bring the
server crashing to its knees? I've been looking in all of my SQL books
(haven't tried the web yet) and can't find anything about this table...can
you point me in the direction of more info?
Thanks,
Rox
"R Goodman" <bobnrox@.verizon.net> wrote in message
news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.4ax.com...
> This query returns 0 rows
>
>
> This query returns 10 rows as it should.
> I guess the next step really is contacting Microsoft...or re-installing
SQL[vbcol=seagreen]
> Server... Thanks for your help.
> Rox
>
>
restoring[vbcol=seagreen]
> with
> for
> Fixed
> time...oops!
> the
there?
> drive
> server
>
|||I don't think spt_values is officially documented in any
Microsoft docs. It's mentioned in some articles and books -
you can find some information in this article:
http://www.winnetmag.com/SQLServer/A...8415/8415.html
and this FAQ post:
http://www.mssqlserver.com/faq/general-sptvalues.asp
I can't remember off the top of my head which books have
more information on it. I'd check those by Kalen Delaney and
Ken Henderson as they'd be most likely to have info on the
table.
But It's essentially just a large lookup table used by SQL
Server functions, stored procedures, etc. With no rows in
the table, you are a bit hosed.
You could do a restore from when you had data in the table.
-Sue
On Tue, 18 May 2004 15:51:13 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:

>Sue,
>OK, I'm looking closer at master.dbo.spt_values...which is empty on that
>server. The production server has lots of stuff in it...I don't think I
>dare even look in this table on the production server...lol
>So what is supposed to be in the spt_values table? Should I look in any
>backups I have of Master to see if I can locate a time when this table
>actually had values and try to restore them? Or will doing that bring the
>server crashing to its knees? I've been looking in all of my SQL books
>(haven't tried the web yet) and can't find anything about this table...can
>you point me in the direction of more info?
>Thanks,
>Rox
>
>
>"R Goodman" <bobnrox@.verizon.net> wrote in message
>news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
>SQL
>restoring
>there?
>

Fixed Database Roles have Disappeared From Enterprise Manager:Security:Server Roles

I'm afraid the answer to this question will fall into the "disaster
recovery" area...one in which I am woefully lacking.
OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
and one for production. I noticed a couple of weeks ago that the Fixed
Server Roles on the QA machine had suddenly disappeared. Everything seemed
to be performing ok, so I didn't give it much thought at the time...oops!
Now I am noticing that scheduled jobs have started failing on that machine
and I'm not sure why. (Side question: where exactly does one find the SQL
Server Logs that failures are supposedly written to?)
Where would these roles have gone to? Why would they have gone there? How
do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive and
reinstall SQL Server......while this isn't the production server - Thank
all that is holy! - the database backups on it have been failing...)
When I run sp_helprole I get:
public 0 0
db_owner 16384 0
db_accessadmin 16385 0
db_securityadmin 16386 0
db_ddladmin 16387 0
db_backupoperator 16389 0
db_datareader 16390 0
db_datawriter 16391 0
db_denydatareader 16392 0
db_denydatawriter 16393 0
When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
tab I get:
(0 row(s) affected)
...when I run sp_helpdbfixedrole on another server I get:
db_accessadmin DB Access Administrators
db_backupoperator DB Backup Operator
db_datareader DB Data Reader
db_datawriter DB Data Writer
db_ddladmin DB DDL Administrators
db_denydatareader DB Deny Data Reader
db_denydatawriter DB Deny Data Writer
db_owner DB Owners
db_securityadmin DB Security Administrators
when I run sp_helprolemember, I only get: db_owner dbo 0x01
when I run sp_helpdb, the db_size column for each database on this server is
null...
TIA,
Rox> and I'm not sure why. (Side question: where exactly does one find the
SQL
> Server Logs that failures are supposedly written to?)
Please ignore the side question...I found them, but they don't seem to have
anything to do with this problem...
Rox|||So what roles seemed to have disappeared? You say that the
fixed server roles disappeared but you are indicating issues
with and running stored procedures to look at the fixed
database roles?
What are the details for the errors on the jobs that are
failing? What are the error details for the backups that are
failing?
Have you run DBCCs on the databases?
-Sue
On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:
>I'm afraid the answer to this question will fall into the "disaster
>recovery" area...one in which I am woefully lacking.
>OK, here goes. We have 3 SQL2000 Servers; one for development; one for QA;
>and one for production. I noticed a couple of weeks ago that the Fixed
>Server Roles on the QA machine had suddenly disappeared. Everything seemed
>to be performing ok, so I didn't give it much thought at the time...oops!
>Now I am noticing that scheduled jobs have started failing on that machine
>and I'm not sure why. (Side question: where exactly does one find the SQL
>Server Logs that failures are supposedly written to?)
>Where would these roles have gone to? Why would they have gone there? How
>do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive and
>reinstall SQL Server......while this isn't the production server - Thank
>all that is holy! - the database backups on it have been failing...)
>When I run sp_helprole I get:
>public 0 0
>db_owner 16384 0
>db_accessadmin 16385 0
>db_securityadmin 16386 0
>db_ddladmin 16387 0
>db_backupoperator 16389 0
>db_datareader 16390 0
>db_datawriter 16391 0
>db_denydatareader 16392 0
>db_denydatawriter 16393 0
>When I run sp_helpdbfixedrole I get nothing on the grid and on the messages
>tab I get:
>(0 row(s) affected)
>...when I run sp_helpdbfixedrole on another server I get:
>db_accessadmin DB Access Administrators
>db_backupoperator DB Backup Operator
>db_datareader DB Data Reader
>db_datawriter DB Data Writer
>db_ddladmin DB DDL Administrators
>db_denydatareader DB Deny Data Reader
>db_denydatawriter DB Deny Data Writer
>db_owner DB Owners
>db_securityadmin DB Security Administrators
>when I run sp_helprolemember, I only get: db_owner dbo 0x01
>when I run sp_helpdb, the db_size column for each database on this server is
>null...
>TIA,
>Rox
>|||> So what roles seemed to have disappeared? You say that the
> fixed server roles disappeared but you are indicating issues
> with and running stored procedures to look at the fixed
> database roles?
> What are the details for the errors on the jobs that are
> failing? What are the error details for the backups that are
> failing?
The fixed roles don't show up in Enterprise Manager or using
sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
doesn't have permissions. I managed to backup the most important DBs
manually (assuming that they are indeed good...I haven't tried restoring
them yet.
> Have you run DBCCs on the databases?
I've run DBCC CheckDatabase on all of the databases (including Master) with
no errors found.
Any ideas that you have would be appreciated.
Thanks,
Rox
> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
> >I'm afraid the answer to this question will fall into the "disaster
> >recovery" area...one in which I am woefully lacking.
> >
> >OK, here goes. We have 3 SQL2000 Servers; one for development; one for
QA;
> >and one for production. I noticed a couple of weeks ago that the Fixed
> >Server Roles on the QA machine had suddenly disappeared. Everything
seemed
> >to be performing ok, so I didn't give it much thought at the time...oops!
> >Now I am noticing that scheduled jobs have started failing on that
machine
> >and I'm not sure why. (Side question: where exactly does one find the
SQL
> >Server Logs that failures are supposedly written to?)
> >
> >Where would these roles have gone to? Why would they have gone there?
How
> >do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive
and
> >reinstall SQL Server......while this isn't the production server -
Thank
> >all that is holy! - the database backups on it have been failing...)
> >
> >When I run sp_helprole I get:
> >public 0 0
> >db_owner 16384 0
> >db_accessadmin 16385 0
> >db_securityadmin 16386 0
> >db_ddladmin 16387 0
> >db_backupoperator 16389 0
> >db_datareader 16390 0
> >db_datawriter 16391 0
> >db_denydatareader 16392 0
> >db_denydatawriter 16393 0
> >
> >When I run sp_helpdbfixedrole I get nothing on the grid and on the
messages
> >tab I get:
> >(0 row(s) affected)
> >
> >...when I run sp_helpdbfixedrole on another server I get:
> >db_accessadmin DB Access Administrators
> >db_backupoperator DB Backup Operator
> >db_datareader DB Data Reader
> >db_datawriter DB Data Writer
> >db_ddladmin DB DDL Administrators
> >db_denydatareader DB Deny Data Reader
> >db_denydatawriter DB Deny Data Writer
> >db_owner DB Owners
> >db_securityadmin DB Security Administrators
> >
> >when I run sp_helprolemember, I only get: db_owner dbo 0x01
> >
> >when I run sp_helpdb, the db_size column for each database on this server
is
> >null...
> >
> >TIA,
> >Rox
> >
> >
>|||I have a feeling that it is time for MS support in your case. I've looked around and I didn't find any system
table where the fixed server roles are stored (but I've might have missed it, of course).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK2MSFTNGP12.phx.gbl...
> > So what roles seemed to have disappeared? You say that the
> > fixed server roles disappeared but you are indicating issues
> > with and running stored procedures to look at the fixed
> > database roles?
> > What are the details for the errors on the jobs that are
> > failing? What are the error details for the backups that are
> > failing?
> The fixed roles don't show up in Enterprise Manager or using
> sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
> doesn't have permissions. I managed to backup the most important DBs
> manually (assuming that they are indeed good...I haven't tried restoring
> them yet.
> > Have you run DBCCs on the databases?
> I've run DBCC CheckDatabase on all of the databases (including Master) with
> no errors found.
> Any ideas that you have would be appreciated.
> Thanks,
> Rox
>
> > On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> > <bobnrox@.verizon.net> wrote:
> >
> > >I'm afraid the answer to this question will fall into the "disaster
> > >recovery" area...one in which I am woefully lacking.
> > >
> > >OK, here goes. We have 3 SQL2000 Servers; one for development; one for
> QA;
> > >and one for production. I noticed a couple of weeks ago that the Fixed
> > >Server Roles on the QA machine had suddenly disappeared. Everything
> seemed
> > >to be performing ok, so I didn't give it much thought at the time...oops!
> > >Now I am noticing that scheduled jobs have started failing on that
> machine
> > >and I'm not sure why. (Side question: where exactly does one find the
> SQL
> > >Server Logs that failures are supposedly written to?)
> > >
> > >Where would these roles have gone to? Why would they have gone there?
> How
> > >do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive
> and
> > >reinstall SQL Server......while this isn't the production server -
> Thank
> > >all that is holy! - the database backups on it have been failing...)
> > >
> > >When I run sp_helprole I get:
> > >public 0 0
> > >db_owner 16384 0
> > >db_accessadmin 16385 0
> > >db_securityadmin 16386 0
> > >db_ddladmin 16387 0
> > >db_backupoperator 16389 0
> > >db_datareader 16390 0
> > >db_datawriter 16391 0
> > >db_denydatareader 16392 0
> > >db_denydatawriter 16393 0
> > >
> > >When I run sp_helpdbfixedrole I get nothing on the grid and on the
> messages
> > >tab I get:
> > >(0 row(s) affected)
> > >
> > >...when I run sp_helpdbfixedrole on another server I get:
> > >db_accessadmin DB Access Administrators
> > >db_backupoperator DB Backup Operator
> > >db_datareader DB Data Reader
> > >db_datawriter DB Data Writer
> > >db_ddladmin DB DDL Administrators
> > >db_denydatareader DB Deny Data Reader
> > >db_denydatawriter DB Deny Data Writer
> > >db_owner DB Owners
> > >db_securityadmin DB Security Administrators
> > >
> > >when I run sp_helprolemember, I only get: db_owner dbo 0x01
> > >
> > >when I run sp_helpdb, the db_size column for each database on this server
> is
> > >null...
> > >
> > >TIA,
> > >Rox
> > >
> > >
> >
>|||For server roles, try executing the following in Query
Analyzer and see if the roles are displayed:
select v1.name as 'Server Role',
v2.name as 'Role Description'
from master.dbo.spt_values v1, master.dbo.spt_values v2
where v1.low = 0 and
v1.type = 'SRV' and
v2.low = -1 and
v2.type = 'SRV' and
v1.number = v2.number
For database roles, try executing the following in Query
analyzer and see if the roles are displayed:
select name as 'Role Name',
uid as 'Role ID',
isapprole as 'Is Application Role'
from sysusers
where issqlrole = 1 or isapprole = 1
-Sue
On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:
>> So what roles seemed to have disappeared? You say that the
>> fixed server roles disappeared but you are indicating issues
>> with and running stored procedures to look at the fixed
>> database roles?
>> What are the details for the errors on the jobs that are
>> failing? What are the error details for the backups that are
>> failing?
>The fixed roles don't show up in Enterprise Manager or using
>sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
>doesn't have permissions. I managed to backup the most important DBs
>manually (assuming that they are indeed good...I haven't tried restoring
>them yet.
>> Have you run DBCCs on the databases?
>I've run DBCC CheckDatabase on all of the databases (including Master) with
>no errors found.
>Any ideas that you have would be appreciated.
>Thanks,
>Rox
>
>> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
>> <bobnrox@.verizon.net> wrote:
>> >I'm afraid the answer to this question will fall into the "disaster
>> >recovery" area...one in which I am woefully lacking.
>> >
>> >OK, here goes. We have 3 SQL2000 Servers; one for development; one for
>QA;
>> >and one for production. I noticed a couple of weeks ago that the Fixed
>> >Server Roles on the QA machine had suddenly disappeared. Everything
>seemed
>> >to be performing ok, so I didn't give it much thought at the time...oops!
>> >Now I am noticing that scheduled jobs have started failing on that
>machine
>> >and I'm not sure why. (Side question: where exactly does one find the
>SQL
>> >Server Logs that failures are supposedly written to?)
>> >
>> >Where would these roles have gone to? Why would they have gone there?
>How
>> >do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive
>and
>> >reinstall SQL Server......while this isn't the production server -
>Thank
>> >all that is holy! - the database backups on it have been failing...)
>> >
>> >When I run sp_helprole I get:
>> >public 0 0
>> >db_owner 16384 0
>> >db_accessadmin 16385 0
>> >db_securityadmin 16386 0
>> >db_ddladmin 16387 0
>> >db_backupoperator 16389 0
>> >db_datareader 16390 0
>> >db_datawriter 16391 0
>> >db_denydatareader 16392 0
>> >db_denydatawriter 16393 0
>> >
>> >When I run sp_helpdbfixedrole I get nothing on the grid and on the
>messages
>> >tab I get:
>> >(0 row(s) affected)
>> >
>> >...when I run sp_helpdbfixedrole on another server I get:
>> >db_accessadmin DB Access Administrators
>> >db_backupoperator DB Backup Operator
>> >db_datareader DB Data Reader
>> >db_datawriter DB Data Writer
>> >db_ddladmin DB DDL Administrators
>> >db_denydatareader DB Deny Data Reader
>> >db_denydatawriter DB Deny Data Writer
>> >db_owner DB Owners
>> >db_securityadmin DB Security Administrators
>> >
>> >when I run sp_helprolemember, I only get: db_owner dbo 0x01
>> >
>> >when I run sp_helpdb, the db_size column for each database on this server
>is
>> >null...
>> >
>> >TIA,
>> >Rox
>> >
>> >
>|||Seems like Sue might have nailed it. The procedure sp_helpdbfixedrole does indeed to a join against spt_values
so it is likely that non-existence of these rows in spt_values will give you the behavior you see...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uh7sJPFPEHA.3896@.TK2MSFTNGP12.phx.gbl...
> I have a feeling that it is time for MS support in your case. I've looked around and I didn't find any
system
> table where the fixed server roles are stored (but I've might have missed it, of course).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "R Goodman" <bobnrox@.verizon.net> wrote in message news:OG6plcDPEHA.904@.TK2MSFTNGP12.phx.gbl...
> > > So what roles seemed to have disappeared? You say that the
> > > fixed server roles disappeared but you are indicating issues
> > > with and running stored procedures to look at the fixed
> > > database roles?
> > > What are the details for the errors on the jobs that are
> > > failing? What are the error details for the backups that are
> > > failing?
> >
> > The fixed roles don't show up in Enterprise Manager or using
> > sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
> > doesn't have permissions. I managed to backup the most important DBs
> > manually (assuming that they are indeed good...I haven't tried restoring
> > them yet.
> >
> > > Have you run DBCCs on the databases?
> >
> > I've run DBCC CheckDatabase on all of the databases (including Master) with
> > no errors found.
> >
> > Any ideas that you have would be appreciated.
> > Thanks,
> > Rox
> >
> >
> > > On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> > > <bobnrox@.verizon.net> wrote:
> > >
> > > >I'm afraid the answer to this question will fall into the "disaster
> > > >recovery" area...one in which I am woefully lacking.
> > > >
> > > >OK, here goes. We have 3 SQL2000 Servers; one for development; one for
> > QA;
> > > >and one for production. I noticed a couple of weeks ago that the Fixed
> > > >Server Roles on the QA machine had suddenly disappeared. Everything
> > seemed
> > > >to be performing ok, so I didn't give it much thought at the time...oops!
> > > >Now I am noticing that scheduled jobs have started failing on that
> > machine
> > > >and I'm not sure why. (Side question: where exactly does one find the
> > SQL
> > > >Server Logs that failures are supposedly written to?)
> > > >
> > > >Where would these roles have gone to? Why would they have gone there?
> > How
> > > >do I get them back? (I'm REALLY hoping the answer isn't: wipe the drive
> > and
> > > >reinstall SQL Server......while this isn't the production server -
> > Thank
> > > >all that is holy! - the database backups on it have been failing...)
> > > >
> > > >When I run sp_helprole I get:
> > > >public 0 0
> > > >db_owner 16384 0
> > > >db_accessadmin 16385 0
> > > >db_securityadmin 16386 0
> > > >db_ddladmin 16387 0
> > > >db_backupoperator 16389 0
> > > >db_datareader 16390 0
> > > >db_datawriter 16391 0
> > > >db_denydatareader 16392 0
> > > >db_denydatawriter 16393 0
> > > >
> > > >When I run sp_helpdbfixedrole I get nothing on the grid and on the
> > messages
> > > >tab I get:
> > > >(0 row(s) affected)
> > > >
> > > >...when I run sp_helpdbfixedrole on another server I get:
> > > >db_accessadmin DB Access Administrators
> > > >db_backupoperator DB Backup Operator
> > > >db_datareader DB Data Reader
> > > >db_datawriter DB Data Writer
> > > >db_ddladmin DB DDL Administrators
> > > >db_denydatareader DB Deny Data Reader
> > > >db_denydatawriter DB Deny Data Writer
> > > >db_owner DB Owners
> > > >db_securityadmin DB Security Administrators
> > > >
> > > >when I run sp_helprolemember, I only get: db_owner dbo 0x01
> > > >
> > > >when I run sp_helpdb, the db_size column for each database on this server
> > is
> > > >null...
> > > >
> > > >TIA,
> > > >Rox
> > > >
> > > >
> > >
> >
> >
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.4ax.com...
> For server roles, try executing the following in Query
> Analyzer and see if the roles are displayed:
> select v1.name as 'Server Role',
> v2.name as 'Role Description'
> from master.dbo.spt_values v1, master.dbo.spt_values v2
> where v1.low = 0 and
> v1.type = 'SRV' and
> v2.low = -1 and
> v2.type = 'SRV' and
> v1.number = v2.number
This query returns 0 rows
> For database roles, try executing the following in Query
> analyzer and see if the roles are displayed:
> select name as 'Role Name',
> uid as 'Role ID',
> isapprole as 'Is Application Role'
> from sysusers
> where issqlrole = 1 or isapprole = 1
This query returns 10 rows as it should.
I guess the next step really is contacting Microsoft...or re-installing SQL
Server... Thanks for your help.
Rox
> -Sue
> On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> <bobnrox@.verizon.net> wrote:
> >> So what roles seemed to have disappeared? You say that the
> >> fixed server roles disappeared but you are indicating issues
> >> with and running stored procedures to look at the fixed
> >> database roles?
> >> What are the details for the errors on the jobs that are
> >> failing? What are the error details for the backups that are
> >> failing?
> >
> >The fixed roles don't show up in Enterprise Manager or using
> >sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
> >doesn't have permissions. I managed to backup the most important DBs
> >manually (assuming that they are indeed good...I haven't tried restoring
> >them yet.
> >
> >> Have you run DBCCs on the databases?
> >
> >I've run DBCC CheckDatabase on all of the databases (including Master)
with
> >no errors found.
> >
> >Any ideas that you have would be appreciated.
> >Thanks,
> >Rox
> >
> >
> >> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> >> <bobnrox@.verizon.net> wrote:
> >>
> >> >I'm afraid the answer to this question will fall into the "disaster
> >> >recovery" area...one in which I am woefully lacking.
> >> >
> >> >OK, here goes. We have 3 SQL2000 Servers; one for development; one
for
> >QA;
> >> >and one for production. I noticed a couple of weeks ago that the
Fixed
> >> >Server Roles on the QA machine had suddenly disappeared. Everything
> >seemed
> >> >to be performing ok, so I didn't give it much thought at the
time...oops!
> >> >Now I am noticing that scheduled jobs have started failing on that
> >machine
> >> >and I'm not sure why. (Side question: where exactly does one find
the
> >SQL
> >> >Server Logs that failures are supposedly written to?)
> >> >
> >> >Where would these roles have gone to? Why would they have gone there?
> >How
> >> >do I get them back? (I'm REALLY hoping the answer isn't: wipe the
drive
> >and
> >> >reinstall SQL Server......while this isn't the production server -
> >Thank
> >> >all that is holy! - the database backups on it have been failing...)
> >> >
> >> >When I run sp_helprole I get:
> >> >public 0 0
> >> >db_owner 16384 0
> >> >db_accessadmin 16385 0
> >> >db_securityadmin 16386 0
> >> >db_ddladmin 16387 0
> >> >db_backupoperator 16389 0
> >> >db_datareader 16390 0
> >> >db_datawriter 16391 0
> >> >db_denydatareader 16392 0
> >> >db_denydatawriter 16393 0
> >> >
> >> >When I run sp_helpdbfixedrole I get nothing on the grid and on the
> >messages
> >> >tab I get:
> >> >(0 row(s) affected)
> >> >
> >> >...when I run sp_helpdbfixedrole on another server I get:
> >> >db_accessadmin DB Access Administrators
> >> >db_backupoperator DB Backup Operator
> >> >db_datareader DB Data Reader
> >> >db_datawriter DB Data Writer
> >> >db_ddladmin DB DDL Administrators
> >> >db_denydatareader DB Deny Data Reader
> >> >db_denydatawriter DB Deny Data Writer
> >> >db_owner DB Owners
> >> >db_securityadmin DB Security Administrators
> >> >
> >> >when I run sp_helprolemember, I only get: db_owner dbo 0x01
> >> >
> >> >when I run sp_helpdb, the db_size column for each database on this
server
> >is
> >> >null...
> >> >
> >> >TIA,
> >> >Rox
> >> >
> >> >
> >>
> >
>|||Sue,
OK, I'm looking closer at master.dbo.spt_values...which is empty on that
server. The production server has lots of stuff in it...I don't think I
dare even look in this table on the production server...lol
So what is supposed to be in the spt_values table? Should I look in any
backups I have of Master to see if I can locate a time when this table
actually had values and try to restore them? Or will doing that bring the
server crashing to its knees? I've been looking in all of my SQL books
(haven't tried the web yet) and can't find anything about this table...can
you point me in the direction of more info?
Thanks,
Rox
"R Goodman" <bobnrox@.verizon.net> wrote in message
news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.4ax.com...
> > For server roles, try executing the following in Query
> > Analyzer and see if the roles are displayed:
> > select v1.name as 'Server Role',
> > v2.name as 'Role Description'
> > from master.dbo.spt_values v1, master.dbo.spt_values v2
> > where v1.low = 0 and
> > v1.type = 'SRV' and
> > v2.low = -1 and
> > v2.type = 'SRV' and
> > v1.number = v2.number
> This query returns 0 rows
>
> >
> > For database roles, try executing the following in Query
> > analyzer and see if the roles are displayed:
> > select name as 'Role Name',
> > uid as 'Role ID',
> > isapprole as 'Is Application Role'
> > from sysusers
> > where issqlrole = 1 or isapprole = 1
> This query returns 10 rows as it should.
> I guess the next step really is contacting Microsoft...or re-installing
SQL
> Server... Thanks for your help.
> Rox
>
>
> >
> > -Sue
> >
> > On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
> > <bobnrox@.verizon.net> wrote:
> >
> > >> So what roles seemed to have disappeared? You say that the
> > >> fixed server roles disappeared but you are indicating issues
> > >> with and running stored procedures to look at the fixed
> > >> database roles?
> > >> What are the details for the errors on the jobs that are
> > >> failing? What are the error details for the backups that are
> > >> failing?
> > >
> > >The fixed roles don't show up in Enterprise Manager or using
> > >sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
> > >doesn't have permissions. I managed to backup the most important DBs
> > >manually (assuming that they are indeed good...I haven't tried
restoring
> > >them yet.
> > >
> > >> Have you run DBCCs on the databases?
> > >
> > >I've run DBCC CheckDatabase on all of the databases (including Master)
> with
> > >no errors found.
> > >
> > >Any ideas that you have would be appreciated.
> > >Thanks,
> > >Rox
> > >
> > >
> > >> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
> > >> <bobnrox@.verizon.net> wrote:
> > >>
> > >> >I'm afraid the answer to this question will fall into the "disaster
> > >> >recovery" area...one in which I am woefully lacking.
> > >> >
> > >> >OK, here goes. We have 3 SQL2000 Servers; one for development; one
> for
> > >QA;
> > >> >and one for production. I noticed a couple of weeks ago that the
> Fixed
> > >> >Server Roles on the QA machine had suddenly disappeared. Everything
> > >seemed
> > >> >to be performing ok, so I didn't give it much thought at the
> time...oops!
> > >> >Now I am noticing that scheduled jobs have started failing on that
> > >machine
> > >> >and I'm not sure why. (Side question: where exactly does one find
> the
> > >SQL
> > >> >Server Logs that failures are supposedly written to?)
> > >> >
> > >> >Where would these roles have gone to? Why would they have gone
there?
> > >How
> > >> >do I get them back? (I'm REALLY hoping the answer isn't: wipe the
> drive
> > >and
> > >> >reinstall SQL Server......while this isn't the production server -
> > >Thank
> > >> >all that is holy! - the database backups on it have been failing...)
> > >> >
> > >> >When I run sp_helprole I get:
> > >> >public 0 0
> > >> >db_owner 16384 0
> > >> >db_accessadmin 16385 0
> > >> >db_securityadmin 16386 0
> > >> >db_ddladmin 16387 0
> > >> >db_backupoperator 16389 0
> > >> >db_datareader 16390 0
> > >> >db_datawriter 16391 0
> > >> >db_denydatareader 16392 0
> > >> >db_denydatawriter 16393 0
> > >> >
> > >> >When I run sp_helpdbfixedrole I get nothing on the grid and on the
> > >messages
> > >> >tab I get:
> > >> >(0 row(s) affected)
> > >> >
> > >> >...when I run sp_helpdbfixedrole on another server I get:
> > >> >db_accessadmin DB Access Administrators
> > >> >db_backupoperator DB Backup Operator
> > >> >db_datareader DB Data Reader
> > >> >db_datawriter DB Data Writer
> > >> >db_ddladmin DB DDL Administrators
> > >> >db_denydatareader DB Deny Data Reader
> > >> >db_denydatawriter DB Deny Data Writer
> > >> >db_owner DB Owners
> > >> >db_securityadmin DB Security Administrators
> > >> >
> > >> >when I run sp_helprolemember, I only get: db_owner dbo 0x01
> > >> >
> > >> >when I run sp_helpdb, the db_size column for each database on this
> server
> > >is
> > >> >null...
> > >> >
> > >> >TIA,
> > >> >Rox
> > >> >
> > >> >
> > >>
> > >
> >
>|||I don't think spt_values is officially documented in any
Microsoft docs. It's mentioned in some articles and books -
you can find some information in this article:
http://www.winnetmag.com/SQLServer/Article/ArticleID/8415/8415.html
and this FAQ post:
http://www.mssqlserver.com/faq/general-sptvalues.asp
I can't remember off the top of my head which books have
more information on it. I'd check those by Kalen Delaney and
Ken Henderson as they'd be most likely to have info on the
table.
But It's essentially just a large lookup table used by SQL
Server functions, stored procedures, etc. With no rows in
the table, you are a bit hosed.
You could do a restore from when you had data in the table.
-Sue
On Tue, 18 May 2004 15:51:13 -0400, "R Goodman"
<bobnrox@.verizon.net> wrote:
>Sue,
>OK, I'm looking closer at master.dbo.spt_values...which is empty on that
>server. The production server has lots of stuff in it...I don't think I
>dare even look in this table on the production server...lol
>So what is supposed to be in the spt_values table? Should I look in any
>backups I have of Master to see if I can locate a time when this table
>actually had values and try to restore them? Or will doing that bring the
>server crashing to its knees? I've been looking in all of my SQL books
>(haven't tried the web yet) and can't find anything about this table...can
>you point me in the direction of more info?
>Thanks,
>Rox
>
>
>"R Goodman" <bobnrox@.verizon.net> wrote in message
>news:%23D5ib$QPEHA.2976@.TK2MSFTNGP10.phx.gbl...
>> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> news:p7pia018m76ffudfk3kpko4a3npqrg9bo0@.4ax.com...
>> > For server roles, try executing the following in Query
>> > Analyzer and see if the roles are displayed:
>> > select v1.name as 'Server Role',
>> > v2.name as 'Role Description'
>> > from master.dbo.spt_values v1, master.dbo.spt_values v2
>> > where v1.low = 0 and
>> > v1.type = 'SRV' and
>> > v2.low = -1 and
>> > v2.type = 'SRV' and
>> > v1.number = v2.number
>> This query returns 0 rows
>>
>> >
>> > For database roles, try executing the following in Query
>> > analyzer and see if the roles are displayed:
>> > select name as 'Role Name',
>> > uid as 'Role ID',
>> > isapprole as 'Is Application Role'
>> > from sysusers
>> > where issqlrole = 1 or isapprole = 1
>> This query returns 10 rows as it should.
>> I guess the next step really is contacting Microsoft...or re-installing
>SQL
>> Server... Thanks for your help.
>> Rox
>>
>>
>> >
>> > -Sue
>> >
>> > On Mon, 17 May 2004 13:47:46 -0400, "R Goodman"
>> > <bobnrox@.verizon.net> wrote:
>> >
>> > >> So what roles seemed to have disappeared? You say that the
>> > >> fixed server roles disappeared but you are indicating issues
>> > >> with and running stored procedures to look at the fixed
>> > >> database roles?
>> > >> What are the details for the errors on the jobs that are
>> > >> failing? What are the error details for the backups that are
>> > >> failing?
>> > >
>> > >The fixed roles don't show up in Enterprise Manager or using
>> > >sp_helpdbfixedrole. The scheduled DTSs that have failed say that Admin
>> > >doesn't have permissions. I managed to backup the most important DBs
>> > >manually (assuming that they are indeed good...I haven't tried
>restoring
>> > >them yet.
>> > >
>> > >> Have you run DBCCs on the databases?
>> > >
>> > >I've run DBCC CheckDatabase on all of the databases (including Master)
>> with
>> > >no errors found.
>> > >
>> > >Any ideas that you have would be appreciated.
>> > >Thanks,
>> > >Rox
>> > >
>> > >
>> > >> On Thu, 13 May 2004 11:07:04 -0400, "R Goodman"
>> > >> <bobnrox@.verizon.net> wrote:
>> > >>
>> > >> >I'm afraid the answer to this question will fall into the "disaster
>> > >> >recovery" area...one in which I am woefully lacking.
>> > >> >
>> > >> >OK, here goes. We have 3 SQL2000 Servers; one for development; one
>> for
>> > >QA;
>> > >> >and one for production. I noticed a couple of weeks ago that the
>> Fixed
>> > >> >Server Roles on the QA machine had suddenly disappeared. Everything
>> > >seemed
>> > >> >to be performing ok, so I didn't give it much thought at the
>> time...oops!
>> > >> >Now I am noticing that scheduled jobs have started failing on that
>> > >machine
>> > >> >and I'm not sure why. (Side question: where exactly does one find
>> the
>> > >SQL
>> > >> >Server Logs that failures are supposedly written to?)
>> > >> >
>> > >> >Where would these roles have gone to? Why would they have gone
>there?
>> > >How
>> > >> >do I get them back? (I'm REALLY hoping the answer isn't: wipe the
>> drive
>> > >and
>> > >> >reinstall SQL Server......while this isn't the production server -
>> > >Thank
>> > >> >all that is holy! - the database backups on it have been failing...)
>> > >> >
>> > >> >When I run sp_helprole I get:
>> > >> >public 0 0
>> > >> >db_owner 16384 0
>> > >> >db_accessadmin 16385 0
>> > >> >db_securityadmin 16386 0
>> > >> >db_ddladmin 16387 0
>> > >> >db_backupoperator 16389 0
>> > >> >db_datareader 16390 0
>> > >> >db_datawriter 16391 0
>> > >> >db_denydatareader 16392 0
>> > >> >db_denydatawriter 16393 0
>> > >> >
>> > >> >When I run sp_helpdbfixedrole I get nothing on the grid and on the
>> > >messages
>> > >> >tab I get:
>> > >> >(0 row(s) affected)
>> > >> >
>> > >> >...when I run sp_helpdbfixedrole on another server I get:
>> > >> >db_accessadmin DB Access Administrators
>> > >> >db_backupoperator DB Backup Operator
>> > >> >db_datareader DB Data Reader
>> > >> >db_datawriter DB Data Writer
>> > >> >db_ddladmin DB DDL Administrators
>> > >> >db_denydatareader DB Deny Data Reader
>> > >> >db_denydatawriter DB Deny Data Writer
>> > >> >db_owner DB Owners
>> > >> >db_securityadmin DB Security Administrators
>> > >> >
>> > >> >when I run sp_helprolemember, I only get: db_owner dbo 0x01
>> > >> >
>> > >> >when I run sp_helpdb, the db_size column for each database on this
>> server
>> > >is
>> > >> >null...
>> > >> >
>> > >> >TIA,
>> > >> >Rox
>> > >> >
>> > >> >
>> > >>
>> > >
>> >
>>
>