Flow Control in stored procedures

A newbie question..

If within a stored procedure there are a list of Stored Procedure calls.

Create Procedure spTest AS

EXEC spMyActionQuery1
EXEC spMyActionQuery2
EXEC spMyActionQuery3
EXEC spMyActionQuery4

GO

Assuming each sub stored procedure executes an action query, does SQL Server ALWAYS wait for the current procedure to finish execution before proceding with the next stored procedure? I believe the answer is yes.The answer IS yes :D

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

sql

FixedHeader + ToggleItem-Autoscrolling

Hi @.All,

SSRS offers for a table control the nice feature "Fixed Header" (so called in the Properties Box) or "Header should remain visible while scrolling" (so called in the context Properties Window). This works fine.

SSRS has an other (auto-)scrolling-feature for drilldown, e.g. when you have a long list of data in the first column of a table, that are toggle-items for drilldown and you click on such a "+" sign for drilldown, SSRS scrolls the toggled item to the top position of the table chart controll. This works fine.

But... with both features together SSRS scrolls the row selected for drilldown under the fixed header!

For the user, it looks like the data has gone. They have to undo the SSRS drilldown-autoscrolling to see the data again they have choosen to drilldown.

The bug is that SSRS doesn't substract the header-hight from the top position of the table chart for the new (auto)scrolling position.

Has anyone found the same problem and has a workaround? E.g. can the mechanism of drilldown-autoscrolling be turned off? With what property?

Thanks for any help in advance!

Cheers,

Mobi

Hi,

Does anyone have a solution for this. Is it a bug and has it been reported to microsoft?

Cheers,

Dottyoz

FixedHeader + ToggleItem-Autoscrolling

Hi @.All,

SSRS offers for a table control the nice feature "Fixed Header" (so called in the Properties Box) or "Header should remain visible while scrolling" (so called in the context Properties Window). This works fine.

SSRS has an other (auto-)scrolling-feature for drilldown, e.g. when you have a long list of data in the first column of a table, that are toggle-items for drilldown and you click on such a "+" sign for drilldown, SSRS scrolls the toggled item to the top position of the table chart controll. This works fine.

But... with both features together SSRS scrolls the row selected for drilldown under the fixed header!

For the user, it looks like the data has gone. They have to undo the SSRS drilldown-autoscrolling to see the data again they have choosen to drilldown.

The bug is that SSRS doesn't substract the header-hight from the top position of the table chart for the new (auto)scrolling position.

Has anyone found the same problem and has a workaround? E.g. can the mechanism of drilldown-autoscrolling be turned off? With what property?

Thanks for any help in advance!

Cheers,

Mobi

Hi,

Does anyone have a solution for this. Is it a bug and has it been reported to microsoft?

Cheers,

Dottyoz

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

Firefox(2?) report rendering problems

Hi, recently added a reportviewer control to a page and it rendered
incorrectly in Firefox 2. So we made a custom report control that calls the
reporting services and renders in HTML 4.0 and the formatting is still messed
up. Basically everything is crammed into the left 5% or so of the page so
all of the text is a jumbled mess. Has anyone tried their reports in Firefox
2 and had success? Anyone encountered this same problem? Thanks!
--
Brian Orrell
Pariveda SolutionsYes, we have the same problems:
Fixed some of them based on info found here:
http://weblogs.asp.net/jgalloway/archive/2006/09/01/SQL-Reporting-Services-_2D00_-CSS-fix-for-Firefox.aspx
Steve MunLeeuw
"Brian Orrell" <BrianOrrell@.community.nospam> wrote in message
news:D5DF5511-A10F-4C87-B584-943798C02E0A@.microsoft.com...
> Hi, recently added a reportviewer control to a page and it rendered
> incorrectly in Firefox 2. So we made a custom report control that calls
> the
> reporting services and renders in HTML 4.0 and the formatting is still
> messed
> up. Basically everything is crammed into the left 5% or so of the page so
> all of the text is a jumbled mess. Has anyone tried their reports in
> Firefox
> 2 and had success? Anyone encountered this same problem? Thanks!
> --
> Brian Orrell
> Pariveda Solutions|||I've been using this:
https://addons.mozilla.org/firefox/1419/
Kevin
"Brian Orrell" <BrianOrrell@.community.nospam> wrote in message
news:D5DF5511-A10F-4C87-B584-943798C02E0A@.microsoft.com...
> Hi, recently added a reportviewer control to a page and it rendered
> incorrectly in Firefox 2. So we made a custom report control that calls
> the
> reporting services and renders in HTML 4.0 and the formatting is still
> messed
> up. Basically everything is crammed into the left 5% or so of the page so
> all of the text is a jumbled mess. Has anyone tried their reports in
> Firefox
> 2 and had success? Anyone encountered this same problem? Thanks!
> --
> Brian Orrell
> Pariveda Solutions