Hi,
I know I am being thick here, and should probably just read the manual, but
how do enable users to log on and view reports. I have gone in to the
Properties of the report folder where the report I want a user to see is
located. I then set the security options to add a user, assigning them the
browser role. The report then inherits that role of the folder. However,
when the user logs in to the site their homepage is completely blank. They
don't get all the buttons and the top and definitely no reports. What do I
still need to do? Please help.
TIA,
JarrydHi,
Nevermind this post. Sussed it out. I was being stupid.
TIA,
Jarryd
"Jarryd" <jarryd@.community.nospam> wrote in message
news:%23MRFbAMvHHA.3660@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I know I am being thick here, and should probably just read the manual,
> but how do enable users to log on and view reports. I have gone in to the
> Properties of the report folder where the report I want a user to see is
> located. I then set the security options to add a user, assigning them
> the browser role. The report then inherits that role of the folder.
> However, when the user logs in to the site their homepage is completely
> blank. They don't get all the buttons and the top and definitely no
> reports. What do I still need to do? Please help.
> TIA,
> Jarryd
>
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Friday, March 30, 2012
How do I allow users to schedule jobs in Management Studio
Does anyone know how I can grant a non sysdba user who has bulkadmin and dbcreator rights to schedule jobs on databases they've created?
The user is a developer and we dont want to give him sysdba rights.
http://www.sql-server-performance.com//faq/?f=137Wednesday, March 28, 2012
How do I add 1000+ New Users to ASPNET_DB?
Ok some company has handed me this .xls file containing a 1000+ users -- their emails (which are to be their user names), and their passwords. Both are in plain text format. I want to add these users to the ASPNET_DB, with the condition that the passwords and userids are encrypted, as they are in the table.
How should I do this?
Thanks very much.
The encryption should be in a client side form. You don't want to encrypt a datatype on the DB end and attempt to decrpt it for display.
Create the Table as normal with both the username and password fields as varchar's. Use the import/export wizard to dump it in.
Encrypt the text box control on your form when entering.
<input id="Password1" type="password" />
It'll match against your varchar.
Adamus
|||No, I do want to encrypt the password and user name actually.|||Why is this a TSQL question? Do you want to perform the encryption on the client? Who will need to decrypt the username/password? What accesses the table? There are many questions and depending on that you can pick a method. Below are some ideas:
1. If you need to encrypt / decrypt the username & password combination on the client then it is best to use some algorithm that you can use on the client. So write a program that will encrypt the Excel data and produce a file. You can then load the file into the database
2. You could also use SQLCLR and use one of the Crypto API classes to do the encryption / decryption. You can then use it on the server-side easily.
And it seems like you should post this in the security newsgroup also. Deciding what level of encryption to use and the purpose of encryption depends on your requirements. The security guys will be able to help on the choice of the encryption algorithm or technology.
How do i ?! Basic Select Statement
I have an sql data source..select command is something like this.
Select password from users where username = Username.Text
how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text
i know its a silly question but i took me long time and i still can't find the ans
We are going to need a little more context to evaluate your exact situation, and how to do what you ask, but here's 1 method:
Dim sqlConn as new SqlConnection("connection string here")
Dim sqlCmd as new SqlCommand("SELECT password from users where username = @.username", SqlConn)
sqlCmd.Paramaters.AddwithValue("@.username", username.text)
Dim DA as new SqlDataAdapter(SqlCmd)
Dim DT as new DataTable
DA.Fill(DT)
Dim myString as string = DT.rows(0).item("password").value ' This line may not be totally correct, doing it from memory and will edit it later
If myString.tolower = stringToCompare.tolower then
'Password is valid
else
'Password could be valid - but something threw you into the else statment, maybe an empty string.
end if
|||
Connection string and select command are already defined in the SqlDataSource.
how can you use the SqlDataSource to extract the value of the password?
i tried to use a hidden GridView but i didn't know how to access a specific cell
by the way i am using C#
|||The above example is a much cleaner method of getting information like this. Putting a hidden gridview on your page is a VERY heavy method of trying to extract a single value, but it can be done.
Please see this article on how to extract a Datatable from the SqlDataSource control. Once you've done that, you can access a specific row of your datatable as mentioned above.
http://msmvps.com/blogs/egoldin/archive/2006/12/27/how-to-get-datatable-out-of-sqldatasource.aspx
I really have to recomend against this method though. If you insist on using the SqlDataSouce control to get this done, consider putting two hidden textbox on the page, and binding the value that comes back from the database to the hidden textbox, then use a compare validator and only let the user continue if page.isvalid.
How do i ?! Basic Select Statement
I have an sql data source..select command is something like this.
Select password from users where username = Username.Text
how can i retrieve the password value and save it into a string?!
to make a comparison between password value and a textbox Password.Text
i know its a silly question but i took me long time and i still can't find the answer
Hello Ahmad,
Here's some code that should help you out... I'd be happy to clarify anything if needed.
using System.Data;
using System.Data.SqlClient;
string userPassword;
string userName;
SqlConnection cnn = new SqlConnection("Data Source = (local); Initial Catalog = nameofdatabase; Integrated Security = True");
SqlCommand cmd = new SqlCommand("select Password From Users Where username = @.username", cnn);
SqlParameter UserName = new SqlParameter("@.username", userName);
cmd.Parameters.Add(UserName);
cnn.Open();
string password = (string)cmd.ExecuteScalar();
cnn.Close();
cnn.Dispose();
if (userPassword == password)
{
// success! Do something here
}
else
{
// Failure...
}
Regards.
sql
How do disable a user to "Generate SQL Scripts"
I have an SQLServer 2000 instance with a DataBase.
I have 2 users: sa, new_user.
The [new_user] has membership role: public, db_datareader, db_datawriter
It has permissions to manipulate some table and stored procedures. He can
not export, import, backup, restore the data base.
The point is that he can generate a script for the all database!!!
How do I disable it?
Can some one give me some help on this issue.
Thanks,
JoaoRegoSince you give the user db_datareader membership, then they can read
anything in the database, including the structure of the tables.
You can deny permissions to system tables like sysobjects to prevent listing
tables for example, like so ->
deny select on sysobjects to user1
This would prevent user1 from getting a list of tables for example.
Another common scheme is to only allow user's access to views and totally
restrict access to the actual underlying tables.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joao Rego" <Joao Rego@.discussions.microsoft.com> wrote in message
news:4B2E9F96-E012-4CF3-8033-A19A6E0DD797@.microsoft.com...
>I have an SQLServer 2000 instance with a DataBase.
> I have 2 users: sa, new_user.
> The [new_user] has membership role: public, db_datareader, db_datawrit
er
> It has permissions to manipulate some table and stored procedures. He can
> not export, import, backup, restore the data base.
> The point is that he can generate a script for the all database!!!
> How do I disable it?
> Can some one give me some help on this issue.
> Thanks,
> JoaoRegosql
I have 2 users: sa, new_user.
The [new_user] has membership role: public, db_datareader, db_datawriter
It has permissions to manipulate some table and stored procedures. He can
not export, import, backup, restore the data base.
The point is that he can generate a script for the all database!!!
How do I disable it?
Can some one give me some help on this issue.
Thanks,
JoaoRegoSince you give the user db_datareader membership, then they can read
anything in the database, including the structure of the tables.
You can deny permissions to system tables like sysobjects to prevent listing
tables for example, like so ->
deny select on sysobjects to user1
This would prevent user1 from getting a list of tables for example.
Another common scheme is to only allow user's access to views and totally
restrict access to the actual underlying tables.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joao Rego" <Joao Rego@.discussions.microsoft.com> wrote in message
news:4B2E9F96-E012-4CF3-8033-A19A6E0DD797@.microsoft.com...
>I have an SQLServer 2000 instance with a DataBase.
> I have 2 users: sa, new_user.
> The [new_user] has membership role: public, db_datareader, db_datawrit
er
> It has permissions to manipulate some table and stored procedures. He can
> not export, import, backup, restore the data base.
> The point is that he can generate a script for the all database!!!
> How do I disable it?
> Can some one give me some help on this issue.
> Thanks,
> JoaoRegosql
Monday, March 26, 2012
How define users of another domain.
Hi everybody, i should have a little problem.
I must grant the access (using odbc) to users of domain "A" to a sql server
2000 instance running in another domain (domain "B").
Well, if the users read the database like user "sa" (defined in the odbc
connection) there aren't problems but if in the odbc connection is specified
the "trust connection" flag nobody can read data.
In sql server the users are defined both username and domain\username and
permissions are defined correctly.
What could i do?
Thanks in avance for your answers.
RobyHi
If your SQL Server is in Domain A, you need to setup a trust relationship
with Domain B at Active Directory level.
Then, when use "B\user1" presents it's credentials to the SQL Server, SQL
Server passes the request to the OS, which in turn passes it to Domain A's
domain controller, who in turn passes the request to Domain B's domain
controller.
The trusting is done at OS level. If you can not setup a trust, then you
need to keep using SQL Server Security.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"robyemme" <marautor@.tiscali.it> wrote in message
news:F1657283-F17A-40FC-9061-C0BFBA7DC569@.microsoft.com...
> Hi everybody, i should have a little problem.
> I must grant the access (using odbc) to users of domain "A" to a sql
> server
> 2000 instance running in another domain (domain "B").
> Well, if the users read the database like user "sa" (defined in the odbc
> connection) there aren't problems but if in the odbc connection is
> specified
> the "trust connection" flag nobody can read data.
> In sql server the users are defined both username and domain\username and
> permissions are defined correctly.
> What could i do?
> Thanks in avance for your answers.
> Roby
>
I must grant the access (using odbc) to users of domain "A" to a sql server
2000 instance running in another domain (domain "B").
Well, if the users read the database like user "sa" (defined in the odbc
connection) there aren't problems but if in the odbc connection is specified
the "trust connection" flag nobody can read data.
In sql server the users are defined both username and domain\username and
permissions are defined correctly.
What could i do?
Thanks in avance for your answers.
RobyHi
If your SQL Server is in Domain A, you need to setup a trust relationship
with Domain B at Active Directory level.
Then, when use "B\user1" presents it's credentials to the SQL Server, SQL
Server passes the request to the OS, which in turn passes it to Domain A's
domain controller, who in turn passes the request to Domain B's domain
controller.
The trusting is done at OS level. If you can not setup a trust, then you
need to keep using SQL Server Security.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"robyemme" <marautor@.tiscali.it> wrote in message
news:F1657283-F17A-40FC-9061-C0BFBA7DC569@.microsoft.com...
> Hi everybody, i should have a little problem.
> I must grant the access (using odbc) to users of domain "A" to a sql
> server
> 2000 instance running in another domain (domain "B").
> Well, if the users read the database like user "sa" (defined in the odbc
> connection) there aren't problems but if in the odbc connection is
> specified
> the "trust connection" flag nobody can read data.
> In sql server the users are defined both username and domain\username and
> permissions are defined correctly.
> What could i do?
> Thanks in avance for your answers.
> Roby
>
Friday, March 23, 2012
How could I notify new subscribers with previous data?
Hi!
I have my NS running. Subscribers are reciving notifications right. How could I send past notifications to new users . Could it be possible?
I think we have all the previous data (the event data table as the notification table) what do you think is the best way or approach to do it?
i.e:
Insert an action item asigned to a user in my DB. If the user has a subscription to it, NS generate a notification and send an email to the user. In my case this user is not a subscriber yet, I add him 2 days after. How could I send the notification thas had been created for him before?
Regards ;)
If I ever have this problem, I will probably play with checking the <myNSApplicationName>Notifications table and look for the data matching the same criteria as those specified by this "late" client in his subscription(s).
...unless it's been already vacuumed, of course.
|||Assuming this is a scheduled subscription, you can use a chronicles tables to keep track of the last time a subscription was processed. For new subscriptions, the datetime column should be set to something like '1900-01-01 00:00:00.000' so that it will match everything.
After the match, you then update the LastProcessedTime column of the chronicles table with the current datetime. That way, future quantums will only send the most recently added event data.
HTH...
Joe
How could I notify new subscribers with previous data?
Hi!
I have my NS running. Subscribers are reciving notifications right. How could I send past notifications to new users . Could it be possible?
I think we have all the previous data (the event data table as the notification table) what do you think is the best way or approach to do it?
i.e:
Insert an action item asigned to a user in my DB. If the user has a subscription to it, NS generate a notification and send an email to the user. In my case this user is not a subscriber yet, I add him 2 days after. How could I send the notification thas had been created for him before?
Regards ;)
If I ever have this problem, I will probably play with checking the <myNSApplicationName>Notifications table and look for the data matching the same criteria as those specified by this "late" client in his subscription(s).
...unless it's been already vacuumed, of course.
|||Assuming this is a scheduled subscription, you can use a chronicles tables to keep track of the last time a subscription was processed. For new subscriptions, the datetime column should be set to something like '1900-01-01 00:00:00.000' so that it will match everything.
After the match, you then update the LastProcessedTime column of the chronicles table with the current datetime. That way, future quantums will only send the most recently added event data.
HTH...
Joe
Wednesday, March 21, 2012
How come i can't add a domain user?
how come when i try to add a domain user/group i get this error:
Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
Check the name again.Hi,
See the reply from Bill from a old postregarding the same issue.
Hi Biva,
I see this is a complex problem as usually the suggestions should work.
The Enterprise Manager will use sp_grantlogin to grant permissions. When we
use sp_grantlogin to grant the permissions to a NT User, the stored
procedure calls
LookupAccountSid function and that function looks in the SID cache on the
local
computer before it goes to a domain controller. If the SID in the cache is
incorrect, then the problem would occur. This problem may occur if we
rename the User account. As far as I know, the only method is to reboot the
computer. However, you may check with Windows 2000 newsgroups for
suggestions, e.g. microsoft.public.win2000.general,
microsoft.public.win2000.active_directory.
In addition, this indicates that domain names are not resolved properly.
Please check in Administators Group in Computer Management. If domains
accounts that are members 'ARE NOT' showing up like this,
domain\account
Instead, they show up as a long string of numbers, then it indicates that
it is not resolved properly. Please check with Windows 2000 newsgroups to
see if they have any suggestions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
Thanks
Hari
"James" <See.My.Sig@.The.Bottom.com> wrote in message
news:s1wIe.13389$Bx5.11303@.trnddc09...
> how come when i try to add a domain user/group i get this error:
> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
> Check the name again.
>
>|||thank you, i found my problem tho, i needed to apply ISA 2004 standard
server's service pack 1. isa doesn't like win2003's sp1... isa's firewall
was stoping rpc.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OTxmREUmFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> See the reply from Bill from a old postregarding the same issue.
> Hi Biva,
>
> I see this is a complex problem as usually the suggestions should work.
>
> The Enterprise Manager will use sp_grantlogin to grant permissions. When
> we
> use sp_grantlogin to grant the permissions to a NT User, the stored
> procedure calls
> LookupAccountSid function and that function looks in the SID cache on the
> local
> computer before it goes to a domain controller. If the SID in the cache is
> incorrect, then the problem would occur. This problem may occur if we
> rename the User account. As far as I know, the only method is to reboot
> the
> computer. However, you may check with Windows 2000 newsgroups for
> suggestions, e.g. microsoft.public.win2000.general,
> microsoft.public.win2000.active_directory.
>
> In addition, this indicates that domain names are not resolved properly.
> Please check in Administators Group in Computer Management. If domains
> accounts that are members 'ARE NOT' showing up like this,
> domain\account
>
> Instead, they show up as a long string of numbers, then it indicates that
> it is not resolved properly. Please check with Windows 2000 newsgroups to
> see if they have any suggestions.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Regards,
>
> Bill Cheng
> Microsoft Support Engineer
>
> Thanks
> Hari
> "James" <See.My.Sig@.The.Bottom.com> wrote in message
> news:s1wIe.13389$Bx5.11303@.trnddc09...
>> how come when i try to add a domain user/group i get this error:
>> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
>> Check the name again.
>>
>>
>
Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
Check the name again.Hi,
See the reply from Bill from a old postregarding the same issue.
Hi Biva,
I see this is a complex problem as usually the suggestions should work.
The Enterprise Manager will use sp_grantlogin to grant permissions. When we
use sp_grantlogin to grant the permissions to a NT User, the stored
procedure calls
LookupAccountSid function and that function looks in the SID cache on the
local
computer before it goes to a domain controller. If the SID in the cache is
incorrect, then the problem would occur. This problem may occur if we
rename the User account. As far as I know, the only method is to reboot the
computer. However, you may check with Windows 2000 newsgroups for
suggestions, e.g. microsoft.public.win2000.general,
microsoft.public.win2000.active_directory.
In addition, this indicates that domain names are not resolved properly.
Please check in Administators Group in Computer Management. If domains
accounts that are members 'ARE NOT' showing up like this,
domain\account
Instead, they show up as a long string of numbers, then it indicates that
it is not resolved properly. Please check with Windows 2000 newsgroups to
see if they have any suggestions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
Thanks
Hari
"James" <See.My.Sig@.The.Bottom.com> wrote in message
news:s1wIe.13389$Bx5.11303@.trnddc09...
> how come when i try to add a domain user/group i get this error:
> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
> Check the name again.
>
>|||thank you, i found my problem tho, i needed to apply ISA 2004 standard
server's service pack 1. isa doesn't like win2003's sp1... isa's firewall
was stoping rpc.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OTxmREUmFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> See the reply from Bill from a old postregarding the same issue.
> Hi Biva,
>
> I see this is a complex problem as usually the suggestions should work.
>
> The Enterprise Manager will use sp_grantlogin to grant permissions. When
> we
> use sp_grantlogin to grant the permissions to a NT User, the stored
> procedure calls
> LookupAccountSid function and that function looks in the SID cache on the
> local
> computer before it goes to a domain controller. If the SID in the cache is
> incorrect, then the problem would occur. This problem may occur if we
> rename the User account. As far as I know, the only method is to reboot
> the
> computer. However, you may check with Windows 2000 newsgroups for
> suggestions, e.g. microsoft.public.win2000.general,
> microsoft.public.win2000.active_directory.
>
> In addition, this indicates that domain names are not resolved properly.
> Please check in Administators Group in Computer Management. If domains
> accounts that are members 'ARE NOT' showing up like this,
> domain\account
>
> Instead, they show up as a long string of numbers, then it indicates that
> it is not resolved properly. Please check with Windows 2000 newsgroups to
> see if they have any suggestions.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Regards,
>
> Bill Cheng
> Microsoft Support Engineer
>
> Thanks
> Hari
> "James" <See.My.Sig@.The.Bottom.com> wrote in message
> news:s1wIe.13389$Bx5.11303@.trnddc09...
>> how come when i try to add a domain user/group i get this error:
>> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
>> Check the name again.
>>
>>
>
How come i can't add a domain user?
how come when i try to add a domain user/group i get this error:
Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
Check the name again.Hi,
See the reply from Bill from a old postregarding the same issue.
Hi Biva,
I see this is a complex problem as usually the suggestions should work.
The Enterprise Manager will use sp_grantlogin to grant permissions. When we
use sp_grantlogin to grant the permissions to a NT User, the stored
procedure calls
LookupAccountSid function and that function looks in the SID cache on the
local
computer before it goes to a domain controller. If the SID in the cache is
incorrect, then the problem would occur. This problem may occur if we
rename the User account. As far as I know, the only method is to reboot the
computer. However, you may check with Windows 2000 newsgroups for
suggestions, e.g. microsoft.public.win2000.gener_al,
microsoft.public.win2000.activ_e_directory.
In addition, this indicates that domain names are not resolved properly.
Please check in Administators Group in Computer Management. If domains
accounts that are members 'ARE NOT' showing up like this,
domain\account
Instead, they show up as a long string of numbers, then it indicates that
it is not resolved properly. Please check with Windows 2000 newsgroups to
see if they have any suggestions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
Thanks
Hari
"James" <See.My.Sig@.The.Bottom.com> wrote in message
news:s1wIe.13389$Bx5.11303@.trnddc09...
> how come when i try to add a domain user/group i get this error:
> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
> Check the name again.
>
>|||thank you, i found my problem tho, i needed to apply ISA 2004 standard
server's service pack 1. ISA doesn't like win2003's sp1... isa's firewall
was stoping rpc.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OTxmREUmFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> See the reply from Bill from a old postregarding the same issue.
> Hi Biva,
>
> I see this is a complex problem as usually the suggestions should work.
>
> The Enterprise Manager will use sp_grantlogin to grant permissions. When
> we
> use sp_grantlogin to grant the permissions to a NT User, the stored
> procedure calls
> LookupAccountSid function and that function looks in the SID cache on the
> local
> computer before it goes to a domain controller. If the SID in the cache is
> incorrect, then the problem would occur. This problem may occur if we
> rename the User account. As far as I know, the only method is to reboot
> the
> computer. However, you may check with Windows 2000 newsgroups for
> suggestions, e.g. microsoft.public.win2000.gener_al,
> microsoft.public.win2000.activ_e_directory.
>
> In addition, this indicates that domain names are not resolved properly.
> Please check in Administators Group in Computer Management. If domains
> accounts that are members 'ARE NOT' showing up like this,
> domain\account
>
> Instead, they show up as a long string of numbers, then it indicates that
> it is not resolved properly. Please check with Windows 2000 newsgroups to
> see if they have any suggestions.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Regards,
>
> Bill Cheng
> Microsoft Support Engineer
>
> Thanks
> Hari
> "James" <See.My.Sig@.The.Bottom.com> wrote in message
> news:s1wIe.13389$Bx5.11303@.trnddc09...
>
Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
Check the name again.Hi,
See the reply from Bill from a old postregarding the same issue.
Hi Biva,
I see this is a complex problem as usually the suggestions should work.
The Enterprise Manager will use sp_grantlogin to grant permissions. When we
use sp_grantlogin to grant the permissions to a NT User, the stored
procedure calls
LookupAccountSid function and that function looks in the SID cache on the
local
computer before it goes to a domain controller. If the SID in the cache is
incorrect, then the problem would occur. This problem may occur if we
rename the User account. As far as I know, the only method is to reboot the
computer. However, you may check with Windows 2000 newsgroups for
suggestions, e.g. microsoft.public.win2000.gener_al,
microsoft.public.win2000.activ_e_directory.
In addition, this indicates that domain names are not resolved properly.
Please check in Administators Group in Computer Management. If domains
accounts that are members 'ARE NOT' showing up like this,
domain\account
Instead, they show up as a long string of numbers, then it indicates that
it is not resolved properly. Please check with Windows 2000 newsgroups to
see if they have any suggestions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
Thanks
Hari
"James" <See.My.Sig@.The.Bottom.com> wrote in message
news:s1wIe.13389$Bx5.11303@.trnddc09...
> how come when i try to add a domain user/group i get this error:
> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
> Check the name again.
>
>|||thank you, i found my problem tho, i needed to apply ISA 2004 standard
server's service pack 1. ISA doesn't like win2003's sp1... isa's firewall
was stoping rpc.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OTxmREUmFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> See the reply from Bill from a old postregarding the same issue.
> Hi Biva,
>
> I see this is a complex problem as usually the suggestions should work.
>
> The Enterprise Manager will use sp_grantlogin to grant permissions. When
> we
> use sp_grantlogin to grant the permissions to a NT User, the stored
> procedure calls
> LookupAccountSid function and that function looks in the SID cache on the
> local
> computer before it goes to a domain controller. If the SID in the cache is
> incorrect, then the problem would occur. This problem may occur if we
> rename the User account. As far as I know, the only method is to reboot
> the
> computer. However, you may check with Windows 2000 newsgroups for
> suggestions, e.g. microsoft.public.win2000.gener_al,
> microsoft.public.win2000.activ_e_directory.
>
> In addition, this indicates that domain names are not resolved properly.
> Please check in Administators Group in Computer Management. If domains
> accounts that are members 'ARE NOT' showing up like this,
> domain\account
>
> Instead, they show up as a long string of numbers, then it indicates that
> it is not resolved properly. Please check with Windows 2000 newsgroups to
> see if they have any suggestions.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Regards,
>
> Bill Cheng
> Microsoft Support Engineer
>
> Thanks
> Hari
> "James" <See.My.Sig@.The.Bottom.com> wrote in message
> news:s1wIe.13389$Bx5.11303@.trnddc09...
>
How come i can't add a domain user?
how come when i try to add a domain user/group i get this error:
Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
Check the name again.
Hi,
See the reply from Bill from a old postregarding the same issue.
Hi Biva,
I see this is a complex problem as usually the suggestions should work.
The Enterprise Manager will use sp_grantlogin to grant permissions. When we
use sp_grantlogin to grant the permissions to a NT User, the stored
procedure calls
LookupAccountSid function and that function looks in the SID cache on the
local
computer before it goes to a domain controller. If the SID in the cache is
incorrect, then the problem would occur. This problem may occur if we
rename the User account. As far as I know, the only method is to reboot the
computer. However, you may check with Windows 2000 newsgroups for
suggestions, e.g. microsoft.public.win2000.generXal,
microsoft.public.win2000.activXe_directory.
In addition, this indicates that domain names are not resolved properly.
Please check in Administators Group in Computer Management. If domains
accounts that are members 'ARE NOT' showing up like this,
domain\account
Instead, they show up as a long string of numbers, then it indicates that
it is not resolved properly. Please check with Windows 2000 newsgroups to
see if they have any suggestions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
Thanks
Hari
"James" <See.My.Sig@.The.Bottom.com> wrote in message
news:s1wIe.13389$Bx5.11303@.trnddc09...
> how come when i try to add a domain user/group i get this error:
> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
> Check the name again.
>
>
|||thank you, i found my problem tho, i needed to apply ISA 2004 standard
server's service pack 1. isa doesn't like win2003's sp1... isa's firewall
was stoping rpc.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OTxmREUmFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> See the reply from Bill from a old postregarding the same issue.
> Hi Biva,
>
> I see this is a complex problem as usually the suggestions should work.
>
> The Enterprise Manager will use sp_grantlogin to grant permissions. When
> we
> use sp_grantlogin to grant the permissions to a NT User, the stored
> procedure calls
> LookupAccountSid function and that function looks in the SID cache on the
> local
> computer before it goes to a domain controller. If the SID in the cache is
> incorrect, then the problem would occur. This problem may occur if we
> rename the User account. As far as I know, the only method is to reboot
> the
> computer. However, you may check with Windows 2000 newsgroups for
> suggestions, e.g. microsoft.public.win2000.generXal,
> microsoft.public.win2000.activXe_directory.
>
> In addition, this indicates that domain names are not resolved properly.
> Please check in Administators Group in Computer Management. If domains
> accounts that are members 'ARE NOT' showing up like this,
> domain\account
>
> Instead, they show up as a long string of numbers, then it indicates that
> it is not resolved properly. Please check with Windows 2000 newsgroups to
> see if they have any suggestions.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Regards,
>
> Bill Cheng
> Microsoft Support Engineer
>
> Thanks
> Hari
> "James" <See.My.Sig@.The.Bottom.com> wrote in message
> news:s1wIe.13389$Bx5.11303@.trnddc09...
>
Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
Check the name again.
Hi,
See the reply from Bill from a old postregarding the same issue.
Hi Biva,
I see this is a complex problem as usually the suggestions should work.
The Enterprise Manager will use sp_grantlogin to grant permissions. When we
use sp_grantlogin to grant the permissions to a NT User, the stored
procedure calls
LookupAccountSid function and that function looks in the SID cache on the
local
computer before it goes to a domain controller. If the SID in the cache is
incorrect, then the problem would occur. This problem may occur if we
rename the User account. As far as I know, the only method is to reboot the
computer. However, you may check with Windows 2000 newsgroups for
suggestions, e.g. microsoft.public.win2000.generXal,
microsoft.public.win2000.activXe_directory.
In addition, this indicates that domain names are not resolved properly.
Please check in Administators Group in Computer Management. If domains
accounts that are members 'ARE NOT' showing up like this,
domain\account
Instead, they show up as a long string of numbers, then it indicates that
it is not resolved properly. Please check with Windows 2000 newsgroups to
see if they have any suggestions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
Thanks
Hari
"James" <See.My.Sig@.The.Bottom.com> wrote in message
news:s1wIe.13389$Bx5.11303@.trnddc09...
> how come when i try to add a domain user/group i get this error:
> Error 15401: Windows NT user or group 'MYDOMAIN\Domain Users' not found.
> Check the name again.
>
>
|||thank you, i found my problem tho, i needed to apply ISA 2004 standard
server's service pack 1. isa doesn't like win2003's sp1... isa's firewall
was stoping rpc.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OTxmREUmFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> See the reply from Bill from a old postregarding the same issue.
> Hi Biva,
>
> I see this is a complex problem as usually the suggestions should work.
>
> The Enterprise Manager will use sp_grantlogin to grant permissions. When
> we
> use sp_grantlogin to grant the permissions to a NT User, the stored
> procedure calls
> LookupAccountSid function and that function looks in the SID cache on the
> local
> computer before it goes to a domain controller. If the SID in the cache is
> incorrect, then the problem would occur. This problem may occur if we
> rename the User account. As far as I know, the only method is to reboot
> the
> computer. However, you may check with Windows 2000 newsgroups for
> suggestions, e.g. microsoft.public.win2000.generXal,
> microsoft.public.win2000.activXe_directory.
>
> In addition, this indicates that domain names are not resolved properly.
> Please check in Administators Group in Computer Management. If domains
> accounts that are members 'ARE NOT' showing up like this,
> domain\account
>
> Instead, they show up as a long string of numbers, then it indicates that
> it is not resolved properly. Please check with Windows 2000 newsgroups to
> see if they have any suggestions.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Regards,
>
> Bill Cheng
> Microsoft Support Engineer
>
> Thanks
> Hari
> "James" <See.My.Sig@.The.Bottom.com> wrote in message
> news:s1wIe.13389$Bx5.11303@.trnddc09...
>
how close all current connections to server?
How do you give users/current connections "the boot" in SQL Server (I'm
actually using MSDE2000).
I want to close all current connections to a database (before
deleting/changing it) - is there a way to close all current connections to a
specified database?
thanks in advance,
Rua Haszard Morris.Try
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Hope this helps.
Dan Guzman
SQL Server MVP
"Rua Haszard Morris" <RuaHaszardMorris@.discussions.microsoft.com> wrote in
message news:F15B6DB3-5213-4D75-8A37-75E406EEE5B5@.microsoft.com...
> How do you give users/current connections "the boot" in SQL Server (I'm
> actually using MSDE2000).
> I want to close all current connections to a database (before
> deleting/changing it) - is there a way to close all current connections to
> a
> specified database?
> thanks in advance,
> Rua Haszard Morris.
actually using MSDE2000).
I want to close all current connections to a database (before
deleting/changing it) - is there a way to close all current connections to a
specified database?
thanks in advance,
Rua Haszard Morris.Try
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Hope this helps.
Dan Guzman
SQL Server MVP
"Rua Haszard Morris" <RuaHaszardMorris@.discussions.microsoft.com> wrote in
message news:F15B6DB3-5213-4D75-8A37-75E406EEE5B5@.microsoft.com...
> How do you give users/current connections "the boot" in SQL Server (I'm
> actually using MSDE2000).
> I want to close all current connections to a database (before
> deleting/changing it) - is there a way to close all current connections to
> a
> specified database?
> thanks in advance,
> Rua Haszard Morris.
how close all current connections to server?
How do you give users/current connections "the boot" in SQL Server (I'm
actually using MSDE2000).
I want to close all current connections to a database (before
deleting/changing it) - is there a way to close all current connections to a
specified database?
thanks in advance,
Rua Haszard Morris.Try
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Hope this helps.
Dan Guzman
SQL Server MVP
"Rua Haszard Morris" <RuaHaszardMorris@.discussions.microsoft.com> wrote in
message news:F15B6DB3-5213-4D75-8A37-75E406EEE5B5@.microsoft.com...
> How do you give users/current connections "the boot" in SQL Server (I'm
> actually using MSDE2000).
> I want to close all current connections to a database (before
> deleting/changing it) - is there a way to close all current connections to
> a
> specified database?
> thanks in advance,
> Rua Haszard Morris.
actually using MSDE2000).
I want to close all current connections to a database (before
deleting/changing it) - is there a way to close all current connections to a
specified database?
thanks in advance,
Rua Haszard Morris.Try
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Hope this helps.
Dan Guzman
SQL Server MVP
"Rua Haszard Morris" <RuaHaszardMorris@.discussions.microsoft.com> wrote in
message news:F15B6DB3-5213-4D75-8A37-75E406EEE5B5@.microsoft.com...
> How do you give users/current connections "the boot" in SQL Server (I'm
> actually using MSDE2000).
> I want to close all current connections to a database (before
> deleting/changing it) - is there a way to close all current connections to
> a
> specified database?
> thanks in advance,
> Rua Haszard Morris.
how close all current connections to server?
How do you give users/current connections "the boot" in SQL Server (I'm
actually using MSDE2000).
I want to close all current connections to a database (before
deleting/changing it) - is there a way to close all current connections to a
specified database?
thanks in advance,
Rua Haszard Morris.
Try
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Hope this helps.
Dan Guzman
SQL Server MVP
"Rua Haszard Morris" <RuaHaszardMorris@.discussions.microsoft.com> wrote in
message news:F15B6DB3-5213-4D75-8A37-75E406EEE5B5@.microsoft.com...
> How do you give users/current connections "the boot" in SQL Server (I'm
> actually using MSDE2000).
> I want to close all current connections to a database (before
> deleting/changing it) - is there a way to close all current connections to
> a
> specified database?
> thanks in advance,
> Rua Haszard Morris.
actually using MSDE2000).
I want to close all current connections to a database (before
deleting/changing it) - is there a way to close all current connections to a
specified database?
thanks in advance,
Rua Haszard Morris.
Try
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Hope this helps.
Dan Guzman
SQL Server MVP
"Rua Haszard Morris" <RuaHaszardMorris@.discussions.microsoft.com> wrote in
message news:F15B6DB3-5213-4D75-8A37-75E406EEE5B5@.microsoft.com...
> How do you give users/current connections "the boot" in SQL Server (I'm
> actually using MSDE2000).
> I want to close all current connections to a database (before
> deleting/changing it) - is there a way to close all current connections to
> a
> specified database?
> thanks in advance,
> Rua Haszard Morris.
Friday, March 9, 2012
How can users "download" cubes to their local machines?
Hi,
I read in oneof the articles somewhere that users can also download cubes to their local machines for off-line browsing/analysis. This can be very useful if the user is suppose away from the office or not connected to the LAN.
I wanted to request if someone can explain the process of how users can do this i.e. download/save a copy of the cube to their local machines?
Thanks.Use Excel
Somewhere is opction to save it as a local cube, use help|||Como Estas rafala
Thanks for the reply. Gracias ;)
So is it correct to assume that downbloading of cubes to local machine is ONLY possible if the front-end being used forcube analysis is Excel?
I'll appreciate a confirmation.|||http://www.winnetmag.com/SQLServer/Article/ArticleID/22394/22394.html|||http://www.winnetmag.com/Article/ArticleID/22394/22394.html
I read in oneof the articles somewhere that users can also download cubes to their local machines for off-line browsing/analysis. This can be very useful if the user is suppose away from the office or not connected to the LAN.
I wanted to request if someone can explain the process of how users can do this i.e. download/save a copy of the cube to their local machines?
Thanks.Use Excel
Somewhere is opction to save it as a local cube, use help|||Como Estas rafala
Thanks for the reply. Gracias ;)
So is it correct to assume that downbloading of cubes to local machine is ONLY possible if the front-end being used forcube analysis is Excel?
I'll appreciate a confirmation.|||http://www.winnetmag.com/SQLServer/Article/ArticleID/22394/22394.html|||http://www.winnetmag.com/Article/ArticleID/22394/22394.html
How can same query be way slower on an identical database?
Hi,
I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
and they are all backup/reporting purposes. So there are no other
users.
I have 2 identical databases with identical tables and identical
stored procedures. Only the data is different. Let me name them DB1
and DB2.
I execute the SP1 on DB1, which has more records than DB2, and it
gives me the results in 2 - 5 minutes.
I execute the SP2 (which is the exact copy of SP1) on DB2, which has
less records than the DB1, and it never gives me the result. After
hours it throws an error that is not specific.
I put indexes, tried to tune it etc. But no use. I still add some
records on both and run the query on Query Analyzer, and get results
for DB1 but not for DB2.
Why would it do this? Is this a database issue or the server issue you
think?
Thanks in advance.
- Denwell... they're not really identical if the rows are different... how have
you verified that schema is the same? Are you just 'sure' or have you used a
tool like DBCompare from redgate?
Also... have you looked at the estimate plans? Do they come back the same?
If not... you might have a statistics difference between the DB's... even if
schema is the same it's certainly possible that auto created stats could be
differernt between the db's...
hope this helps a bit,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"DenoxiS" <google@.deniznet.com> wrote in message
news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> Hi,
> I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> and they are all backup/reporting purposes. So there are no other
> users.
> I have 2 identical databases with identical tables and identical
> stored procedures. Only the data is different. Let me name them DB1
> and DB2.
> I execute the SP1 on DB1, which has more records than DB2, and it
> gives me the results in 2 - 5 minutes.
> I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> less records than the DB1, and it never gives me the result. After
> hours it throws an error that is not specific.
> I put indexes, tried to tune it etc. But no use. I still add some
> records on both and run the query on Query Analyzer, and get results
> for DB1 but not for DB2.
> Why would it do this? Is this a database issue or the server issue you
> think?
> Thanks in advance.
> - Den|||If you are performing any kind of iterative calculation in the stored
procedure it could be that there is data on DB2 that is causing an infinite
loop. If the data on DB1 is "good" it would not have this problem. I have
run into this most often when handling date calculations.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:u51B%23TS7DHA.3420@.TK2MSFTNGP11.phx.gbl...
> well... they're not really identical if the rows are different... how have
> you verified that schema is the same? Are you just 'sure' or have you used
a
> tool like DBCompare from redgate?
> Also... have you looked at the estimate plans? Do they come back the same?
> If not... you might have a statistics difference between the DB's... even
if
> schema is the same it's certainly possible that auto created stats could
be
> differernt between the db's...
> hope this helps a bit,
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "DenoxiS" <google@.deniznet.com> wrote in message
> news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> > Hi,
> >
> > I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> > and they are all backup/reporting purposes. So there are no other
> > users.
> >
> > I have 2 identical databases with identical tables and identical
> > stored procedures. Only the data is different. Let me name them DB1
> > and DB2.
> >
> > I execute the SP1 on DB1, which has more records than DB2, and it
> > gives me the results in 2 - 5 minutes.
> >
> > I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> > less records than the DB1, and it never gives me the result. After
> > hours it throws an error that is not specific.
> >
> > I put indexes, tried to tune it etc. But no use. I still add some
> > records on both and run the query on Query Analyzer, and get results
> > for DB1 but not for DB2.
> >
> > Why would it do this? Is this a database issue or the server issue you
> > think?
> >
> > Thanks in advance.
> >
> > - Den
>|||Take a look at the selectivity of each set of tables. Is the data very similar ie is the distribution of data similar between the tables? Taking a better look at the generated query plans will provide you with the answers you are seeking.
I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
and they are all backup/reporting purposes. So there are no other
users.
I have 2 identical databases with identical tables and identical
stored procedures. Only the data is different. Let me name them DB1
and DB2.
I execute the SP1 on DB1, which has more records than DB2, and it
gives me the results in 2 - 5 minutes.
I execute the SP2 (which is the exact copy of SP1) on DB2, which has
less records than the DB1, and it never gives me the result. After
hours it throws an error that is not specific.
I put indexes, tried to tune it etc. But no use. I still add some
records on both and run the query on Query Analyzer, and get results
for DB1 but not for DB2.
Why would it do this? Is this a database issue or the server issue you
think?
Thanks in advance.
- Denwell... they're not really identical if the rows are different... how have
you verified that schema is the same? Are you just 'sure' or have you used a
tool like DBCompare from redgate?
Also... have you looked at the estimate plans? Do they come back the same?
If not... you might have a statistics difference between the DB's... even if
schema is the same it's certainly possible that auto created stats could be
differernt between the db's...
hope this helps a bit,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"DenoxiS" <google@.deniznet.com> wrote in message
news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> Hi,
> I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> and they are all backup/reporting purposes. So there are no other
> users.
> I have 2 identical databases with identical tables and identical
> stored procedures. Only the data is different. Let me name them DB1
> and DB2.
> I execute the SP1 on DB1, which has more records than DB2, and it
> gives me the results in 2 - 5 minutes.
> I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> less records than the DB1, and it never gives me the result. After
> hours it throws an error that is not specific.
> I put indexes, tried to tune it etc. But no use. I still add some
> records on both and run the query on Query Analyzer, and get results
> for DB1 but not for DB2.
> Why would it do this? Is this a database issue or the server issue you
> think?
> Thanks in advance.
> - Den|||If you are performing any kind of iterative calculation in the stored
procedure it could be that there is data on DB2 that is causing an infinite
loop. If the data on DB1 is "good" it would not have this problem. I have
run into this most often when handling date calculations.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:u51B%23TS7DHA.3420@.TK2MSFTNGP11.phx.gbl...
> well... they're not really identical if the rows are different... how have
> you verified that schema is the same? Are you just 'sure' or have you used
a
> tool like DBCompare from redgate?
> Also... have you looked at the estimate plans? Do they come back the same?
> If not... you might have a statistics difference between the DB's... even
if
> schema is the same it's certainly possible that auto created stats could
be
> differernt between the db's...
> hope this helps a bit,
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "DenoxiS" <google@.deniznet.com> wrote in message
> news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> > Hi,
> >
> > I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> > and they are all backup/reporting purposes. So there are no other
> > users.
> >
> > I have 2 identical databases with identical tables and identical
> > stored procedures. Only the data is different. Let me name them DB1
> > and DB2.
> >
> > I execute the SP1 on DB1, which has more records than DB2, and it
> > gives me the results in 2 - 5 minutes.
> >
> > I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> > less records than the DB1, and it never gives me the result. After
> > hours it throws an error that is not specific.
> >
> > I put indexes, tried to tune it etc. But no use. I still add some
> > records on both and run the query on Query Analyzer, and get results
> > for DB1 but not for DB2.
> >
> > Why would it do this? Is this a database issue or the server issue you
> > think?
> >
> > Thanks in advance.
> >
> > - Den
>|||Take a look at the selectivity of each set of tables. Is the data very similar ie is the distribution of data similar between the tables? Taking a better look at the generated query plans will provide you with the answers you are seeking.
How can one user view other users that belong to a database role?
After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.
In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.
In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.
Any advice on how to duplicate the behavior from SQL2000?
The procedure relies on the security catalog views which are user sensitive, the query which is executed behind the scenes will bring back only the roles for the user.select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the post, however you've only explained why it's behaving as it does. I'm looking for advice on duplicating the Sql2000 behavior. For example, if I log on as user A I'd like to know if user B is a member of role ABC.|||Look what the Workbench does behind the scenes, it will execute the following commands:set NOCOUNT OFF
CREATE TABLE #tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)
declare @.generation int
set @.generation = 0
INSERT INTO #tmp_role_member_ids (id) SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = 'R')and(rl.name=N'db_owner')
UPDATE #tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@.generation
WHILE ( 1=1 )
BEGIN
INSERT INTO #tmp_role_member_ids (id, role_id, sub_role_id, generation)
SELECT a.member_principal_id, b.role_id, a.role_principal_id, @.generation + 1
FROM sys.database_role_members AS a INNER JOIN #tmp_role_member_ids AS b
ON a.role_principal_id = b.id
WHERE b.generation = @.generation
if @.@.ROWCOUNT <= 0
break
set @.generation = @.generation + 1
END
DELETE #tmp_role_member_ids WHERE id in (SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = 'R')and(rl.name=N'db_owner') )
UPDATE #tmp_role_member_ids SET generation = 0;
INSERT INTO #tmp_role_member_ids (id, role_id, generation)
SELECT distinct id, role_id, 1 FROM #tmp_role_member_ids
DELETE #tmp_role_member_ids WHERE generation = 0
SELECT
u.name AS [Name]
FROM
sys.database_principals AS rl
INNER JOIN #tmp_role_member_ids AS m ON m.role_id=rl.principal_id
INNER JOIN sys.database_principals AS u ON u.principal_id = m.id
WHERE
(rl.type = 'R')and(rl.name=N'db_owner')
ORDER BY
[Name] ASC
drop table #tmp_role_member_ids
HTH, Jens K. Suessmeyer.
-
http://www.sqlserver2005.de
-
How can one user view other users that belong to a database role?
After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.
In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.
In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.
Any advice on how to duplicate the behavior from SQL2000?
The procedure relies on the security catalog views which are user sensitive, the query which is executed behind the scenes will bring back only the roles for the user.select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the post, however you've only explained why it's behaving as it does. I'm looking for advice on duplicating the Sql2000 behavior. For example, if I log on as user A I'd like to know if user B is a member of role ABC.|||Look what the Workbench does behind the scenes, it will execute the following commands:set NOCOUNT OFF
CREATE TABLE #tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)
declare @.generation int
set @.generation = 0
INSERT INTO #tmp_role_member_ids (id) SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = 'R')and(rl.name=N'db_owner')
UPDATE #tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@.generation
WHILE ( 1=1 )
BEGIN
INSERT INTO #tmp_role_member_ids (id, role_id, sub_role_id, generation)
SELECT a.member_principal_id, b.role_id, a.role_principal_id, @.generation + 1
FROM sys.database_role_members AS a INNER JOIN #tmp_role_member_ids AS b
ON a.role_principal_id = b.id
WHERE b.generation = @.generation
if @.@.ROWCOUNT <= 0
break
set @.generation = @.generation + 1
END
DELETE #tmp_role_member_ids WHERE id in (SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = 'R')and(rl.name=N'db_owner') )
UPDATE #tmp_role_member_ids SET generation = 0;
INSERT INTO #tmp_role_member_ids (id, role_id, generation)
SELECT distinct id, role_id, 1 FROM #tmp_role_member_ids
DELETE #tmp_role_member_ids WHERE generation = 0
SELECT
u.name AS [Name]
FROM
sys.database_principals AS rl
INNER JOIN #tmp_role_member_ids AS m ON m.role_id=rl.principal_id
INNER JOIN sys.database_principals AS u ON u.principal_id = m.id
WHERE
(rl.type = 'R')and(rl.name=N'db_owner')
ORDER BY
[Name] ASC
drop table #tmp_role_member_ids
HTH, Jens K. Suessmeyer.
-
http://www.sqlserver2005.de
-
Friday, February 24, 2012
How can i use default schemas of users in a function ?
Hi,
I've a Problem with a Function called by different users.
I want the different callers to query a table in their own default Schema.
So if i refence the full Path [Schema].[Tablename] it works Properly.
If i just Use the [Tablename] for reference it fails.
The function was created with EXECUTE AS CALLER option ;
if I call it with dynamic SQL i retreive the error-message
"Only functions and extended stored procedures can be executed from within a function."
does anybody know a solution for this problem?
thanks in advance
Raimund
Raimund wrote:
So if i refence the full Path [Schema].[Tablename] it works Properly.
If i just Use the [Tablename] for reference it fails.
The function was created with EXECUTE AS CALLER option ;
I quote from Books Online (search for "Object Visibility and Qualification Rules")
"
Because LoginX is associated with UserA in DBY, SQL Server first looks for DBY.UserA.TableX. If there is no table with this name, SQL Server looks for a table DBY.dbo.TableX. "
So engine look for [yourCallerUser].[Tablename] and for [dbo].[Tablename] and find no [Tablename]
Then I think you have to maintain full path [Schema].[Tablename] to objects reference.
|||
Hm,
so i guess i have to path the query-results by parameter to the function.
It looks horrible but it works.
Best Regards
Raimund
Subscribe to:
Posts (Atom)